Determine top 25 rows in a table that includes a ntext data type.

We have a table that has ~900,000 rows in the table. The table previously had
~1.5 million rows of data when the ~600,000 rows where deleted the table size
shrunk ~1 G. The table is currently useing ~27 GB of storage.
I would like to write a query to determine my largest 25 rows. This is
being done to determine if the storage is truely being used.
C,
Perhaps:
SELECT TOP 25 RowKey, LEN(NTextColumn)
FROM YourTable
ORDER BY LEN(NTextColumn) DESC
RLF
"C RAMSEY" <CRAMSEY@.discussions.microsoft.com> wrote in message
news:B84581A5-1BB6-4495-B68C-FB65E250E917@.microsoft.com...
> We have a table that has ~900,000 rows in the table. The table previously
> had
> ~1.5 million rows of data when the ~600,000 rows where deleted the table
> size
> shrunk ~1 G. The table is currently useing ~27 GB of storage.
> I would like to write a query to determine my largest 25 rows. This is
> being done to determine if the storage is truely being used.
|||I'm attemping to run it now. I had to use DATALENGTH instead of LEN for the
NTEXT field.
I was hoping to find a was to gather the information using system tables ie
number of pages used by row.
"Russell Fields" wrote:

> C,
> Perhaps:
> SELECT TOP 25 RowKey, LEN(NTextColumn)
> FROM YourTable
> ORDER BY LEN(NTextColumn) DESC
> RLF
> "C RAMSEY" <CRAMSEY@.discussions.microsoft.com> wrote in message
> news:B84581A5-1BB6-4495-B68C-FB65E250E917@.microsoft.com...
>
>
|||> I was hoping to find a was to gather the information using system tables
> ie
> number of pages used by row.
No system table stores this information... there might be some undocumented
DBCC command that does it, but even that would probably be coming from the
page angle, not the row angle.
|||That's what I was affraid of. I used DBCC IND to get the page information.
The other technique was taking to long to run so I stopped it.
"Aaron Bertrand [SQL Server MVP]" wrote:

> No system table stores this information... there might be some undocumented
> DBCC command that does it, but even that would probably be coming from the
> page angle, not the row angle.
>
>
|||> That's what I was affraid of. I used DBCC IND to get the page
> information.
Did you figure out the top 25 rows already? If not...

> The other technique was taking to long to run so I stopped it.
...maybe you could try doing this in two steps, e.g.
SELECT pk, tcl = DATALENGTH(text_col) INTO #foo FROM main_table;
SELECT TOP 25 pk, tcl FROM #foo ORDER BY tcl DESC;
This might be a lot cheaper then including the datalength() calculation and
the ordering in the same step.
|||C RAMSEY wrote:
> We have a table that has ~900,000 rows in the table. The table previously had
> ~1.5 million rows of data when the ~600,000 rows where deleted the table size
> shrunk ~1 G. The table is currently useing ~27 GB of storage.
> I would like to write a query to determine my largest 25 rows. This is
> being done to determine if the storage is truely being used.
When you delete rows, the space that was occupied might not be released.
It might be retained as "unused space", and reused for future rows.
If it is your goal to lower the used storage, then you could first run
sp_spaceused to determine the amount of unused space and/or DBCC
SHOWCONTIG to determine the average page density. If there is a lot of
unused space, then make sure you have a clustered index (or add one) and
run DBCC INDEXDEFRAG or DBCC DBREINDEX / ALTER TABLE to remove the
unused space.
HTH,
Gert-Jan

Determine top 25 rows in a table that includes a ntext data type.

We have a table that has ~900,000 rows in the table. The table previously had
~1.5 million rows of data when the ~600,000 rows where deleted the table size
shrunk ~1 G. The table is currently useing ~27 GB of storage.
I would like to write a query to determine my largest 25 rows. This is
being done to determine if the storage is truely being used.C,
Perhaps:
SELECT TOP 25 RowKey, LEN(NTextColumn)
FROM YourTable
ORDER BY LEN(NTextColumn) DESC
RLF
"C RAMSEY" <CRAMSEY@.discussions.microsoft.com> wrote in message
news:B84581A5-1BB6-4495-B68C-FB65E250E917@.microsoft.com...
> We have a table that has ~900,000 rows in the table. The table previously
> had
> ~1.5 million rows of data when the ~600,000 rows where deleted the table
> size
> shrunk ~1 G. The table is currently useing ~27 GB of storage.
> I would like to write a query to determine my largest 25 rows. This is
> being done to determine if the storage is truely being used.|||I'm attemping to run it now. I had to use DATALENGTH instead of LEN for the
NTEXT field.
I was hoping to find a was to gather the information using system tables ie
number of pages used by row.
"Russell Fields" wrote:
> C,
> Perhaps:
> SELECT TOP 25 RowKey, LEN(NTextColumn)
> FROM YourTable
> ORDER BY LEN(NTextColumn) DESC
> RLF
> "C RAMSEY" <CRAMSEY@.discussions.microsoft.com> wrote in message
> news:B84581A5-1BB6-4495-B68C-FB65E250E917@.microsoft.com...
> > We have a table that has ~900,000 rows in the table. The table previously
> > had
> > ~1.5 million rows of data when the ~600,000 rows where deleted the table
> > size
> > shrunk ~1 G. The table is currently useing ~27 GB of storage.
> >
> > I would like to write a query to determine my largest 25 rows. This is
> > being done to determine if the storage is truely being used.
>
>|||> I was hoping to find a was to gather the information using system tables
> ie
> number of pages used by row.
No system table stores this information... there might be some undocumented
DBCC command that does it, but even that would probably be coming from the
page angle, not the row angle.|||That's what I was affraid of. I used DBCC IND to get the page information.
The other technique was taking to long to run so I stopped it.
"Aaron Bertrand [SQL Server MVP]" wrote:
> > I was hoping to find a was to gather the information using system tables
> > ie
> > number of pages used by row.
> No system table stores this information... there might be some undocumented
> DBCC command that does it, but even that would probably be coming from the
> page angle, not the row angle.
>
>|||> That's what I was affraid of. I used DBCC IND to get the page
> information.
Did you figure out the top 25 rows already? If not...
> The other technique was taking to long to run so I stopped it.
...maybe you could try doing this in two steps, e.g.
SELECT pk, tcl = DATALENGTH(text_col) INTO #foo FROM main_table;
SELECT TOP 25 pk, tcl FROM #foo ORDER BY tcl DESC;
This might be a lot cheaper then including the datalength() calculation and
the ordering in the same step.|||C RAMSEY wrote:
> We have a table that has ~900,000 rows in the table. The table previously had
> ~1.5 million rows of data when the ~600,000 rows where deleted the table size
> shrunk ~1 G. The table is currently useing ~27 GB of storage.
> I would like to write a query to determine my largest 25 rows. This is
> being done to determine if the storage is truely being used.
When you delete rows, the space that was occupied might not be released.
It might be retained as "unused space", and reused for future rows.
If it is your goal to lower the used storage, then you could first run
sp_spaceused to determine the amount of unused space and/or DBCC
SHOWCONTIG to determine the average page density. If there is a lot of
unused space, then make sure you have a clustered index (or add one) and
run DBCC INDEXDEFRAG or DBCC DBREINDEX / ALTER TABLE to remove the
unused space.
HTH,
Gert-Jansql

Determine date when each user last accessed the database

I need to clean up some security issues. We have numerous users who access the database and I'm not confident which logins can be deleted. I've already done sp_who and sp_who2 and neither of them gave me exactly what I wanted (I know they didn't an employee who was terminated but certainly access the database numerous times before he left). I also looked at the sys.sysprocesses table. Is there another system table that can be tapped into for this?

Thanks!

It depends on whether you're looking for a change to the objects or simply an access to the data. For data, you need to set some things up to track who has looked at it recently - and that will only work if they used an application that reports who the user was, such as SQL Server Management Studio. If you have not set the system up to track data access or the application they used to get to the data doesn't report the accurate name of the user, then you won't be able to go back in time to find out what they did.

Thanks -

Buck Woody

|||

One option would be to create a trigger to populate a table of user logins. This might be resource intensive if there is a lot of login activity.

Or, you can turn on login auditing for successful logins and search through the Event log.

You can use a third-party database auditing tool.