Hi the admin dbase which has increased in size from 200 mb to 66 gb in a month ! Is there any way I can find out why ?
I am new to sql administration and would love to find out more and also assist with resolving this issue.
Thanks
Are you doing backups?
Open a query window in SSMS and run this query:
SELECT size_in_GB = 8.192*size/1000. ,* FROM sys.database_files
Usually you will get two rows one for the log file, and one for the data file.
If the data file is taking up most of that 66GB you are seeing, then it really is that a lot of new data came into the database. There is not a lot you can do about it other than investigate if the data is all legitimate, or whether it is some customer logging activity or some such that should be pruned periodically.
If the log file is consuming a lot of space, this either because the recovery model of your database is full or bulk logged and you are not doing backups, or it could be that there were some large queries that needed a lot of space and the space was not released back to the OS.
You can find your recover model using the following query
SELECT name, recovery_model_desc FROM sys.databases
You can find how much space in the log file is actually in use using the following query.
DBCC SQLPERF(LOGSPACE)
If you find that only a small portion of the log space is used,you can shrink the log file (specifically, the log file. DO NOT shrink the data file).
If a good percentage of the log space is in use, backup your log file and then shrink the log file (or alternatively, depending on your recovery requirements, change the recovery model to SIMPLE and then shrink the log file).
If you are going to retain the full recovery model you should schedule regular log backups.