Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2008
    Location
    Leyland in NW England, UK
    Posts
    19

    Question Unanswered: Amount of Memory Data in a Row

    Have recently had issues with lack of space on our SQL Server 2000.

    For some reason the SHRINKDATABASE command was not working on one of the main databases, although it would work OK on others such as pubs or Northwind.

    Eventually we got it to work but in the process of doing so noticed our D drive, which holds both data files & Trans-log files (it was like that when I got here!) is badly fragmented.

    Defragging seems to have no effect but in wondering why drive so fragged got thinking about the row size of tables in DBs. Obviously rows are not supposed to be longer than 8KB - the size of a page - as a single record must fit in a single page.

    How can I check if the actual length of a row is greater than 8K? Is there any statement I can run?

    If anyone has any additional info about clever ways to create space in DBs on server then would be very grateful.

    Sugsy

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    you can write a script against syscolumns that generates another script for each table in the database, like this:
    select max(datalength(col1))+ max(datalength(col2))+ max(datalength(col3))
    from dbo.my_table1
    You can also store the output into a table by adding each table name per row.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  3. #3
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Code:
    set nocount on
    declare @id int, @name sysname, @cmd varchar(8000)
    declare c cursor for
       select id, name=user_name(uid)+'.'+name from sysobjects
       where objectproperty(id, 'IsUserTable') = 1
       order by name
    open c
    fetch next from c into @id, @name
    while @@fetch_status = 0 begin
       set @cmd = 'select '
       select @cmd = @cmd +
          case when @cmd = 'select ' then '' else ',' end + 'max(datalength(' + name + '))'
          from syscolumns where id = @id order by colorder
       set @cmd = @cmd + ' from ' + @name
       fetch next from c into @id, @name
       print @cmd
    end
    close c
    deallocate c
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •