Hi,
I had the problem that my databases grow and I had no idea why... (in the end I found out that it was a badly developed function, but this article will focus on the way to find the item which is growing...)
My entry point was an error on my sql server. (The agent sends me errors with severity > 16 by mail, so the real entry point was the mail. I double checked the error after the mail using xp_readerrorlog). The error indicated that it was not possible to store any data to the disk, because it was full (what is bad).
To get the information which folder is the bad guy in this scenario I used a freeware tool called tree-size. It did exactly what I needed. The tool shows which folder takes which amount of data in percent. ( download page e.g.: http://www.chip.de/downloads/TreeSize-Free_13001595.html ) I was lucky because the data folder of my database showed up as the folder that took 90% of the storage. Even more luckily I found the mdf file (database file) of a specific database which showed up as the problem.
Next step was to make some space and restart my database (I'm not sure if this is an optional step or not, but I would always do that) and to stop my corresponding application. I connected to the database and wanted to know how space usage looked like. So I called (after some minutes of google-ing)
sp_spaceused
the result was the information about the current database... and with the first parameter I can check my table (select * from sys.tables shows a list of tables, so I can use: sp_spaceused 'mytablename'). Now I needed a trick to evaluate all tables (and to evaluate them in only one record set to filter them)... and here it is:
the result of this statement is list of tables with their corresponding information. One of these entries took 90% of the whole space. This was the entry to concentrate on...
how sp_spaceused can help me:
sp_spaceused shows a list of tables and indexed views. Used without parameters the output shows 2 result sets:
- general database level: name, size, unallocated space (<-- see shrink databases)
- general data info: reserved, data, index_size, unused
used with parameter (object name):
- table level: name, rows, reserved, data, index_size, unused
second parameter indicates whether the data should be recalculated or not (@updateusage).
kind regards,
daniel
I had the problem that my databases grow and I had no idea why... (in the end I found out that it was a badly developed function, but this article will focus on the way to find the item which is growing...)
My entry point was an error on my sql server. (The agent sends me errors with severity > 16 by mail, so the real entry point was the mail. I double checked the error after the mail using xp_readerrorlog). The error indicated that it was not possible to store any data to the disk, because it was full (what is bad).
To get the information which folder is the bad guy in this scenario I used a freeware tool called tree-size. It did exactly what I needed. The tool shows which folder takes which amount of data in percent. ( download page e.g.: http://www.chip.de/downloads/TreeSize-Free_13001595.html ) I was lucky because the data folder of my database showed up as the folder that took 90% of the storage. Even more luckily I found the mdf file (database file) of a specific database which showed up as the problem.
Next step was to make some space and restart my database (I'm not sure if this is an optional step or not, but I would always do that) and to stop my corresponding application. I connected to the database and wanted to know how space usage looked like. So I called (after some minutes of google-ing)
sp_spaceused
the result was the information about the current database... and with the first parameter I can check my table (select * from sys.tables shows a list of tables, so I can use: sp_spaceused 'mytablename'). Now I needed a trick to evaluate all tables (and to evaluate them in only one record set to filter them)... and here it is:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | CREATE TABLE #tmp ( ID INT IDENTITY(1,1) ,name NVARCHAR(75) ,rows INT ,reserved NVARCHAR(50) ,data NVARCHAR(50) ,index_size NVARCHAR(50) ,unsed NVARCHAR(50) ) INSERT INTO #tmp EXEC sp_MSforeachtable 'EXEC sp_spaceused ''?''' SELECT * FROM #tmp DROP TABLE #tmp |
the result of this statement is list of tables with their corresponding information. One of these entries took 90% of the whole space. This was the entry to concentrate on...
how sp_spaceused can help me:
sp_spaceused shows a list of tables and indexed views. Used without parameters the output shows 2 result sets:
- general database level: name, size, unallocated space (<-- see shrink databases)
- general data info: reserved, data, index_size, unused
used with parameter (object name):
- table level: name, rows, reserved, data, index_size, unused
second parameter indicates whether the data should be recalculated or not (@updateusage).
kind regards,
daniel
No comments:
Post a Comment