Hi,
The SQL Server Database hangs overnight and also consumes high disk space on one of our servers. This has been recurring for quite a few weeks and occurs daily.
Can somebody assist me in trouble-shooting the same
Thanks in Advance
Deepak
I inherited a SQL server (2005) that sits on a Windows 2003 box (upgrading to 2008 R2 and Win2008, ASAP) and on our SQL Data Drive (260GB - 22GB free) we have 16 databases (that are associated with our primary application). One database in particular is causing me quite a bit of pain - on Tuesdays. This table will consume all available free space (save 700kb) and place it within it's 'reserved' space. It's not being used and I can reclaim it by shrinking. I have done this for 3 consecutive weeks now and I have always thought that shrinking is not really a best practice.
Based on a job I scheduled to spit out various database stats I discovered the following:
Somewhere after 11pm on Monday (and on or before 11:30pm) the reserved space goes from 9GB down to 23 then 37MB on this particular database.
After 1:30am on Tuesday (and on or before 2:00am) the database consumes nearly all available disk space and thus expands its reserved space to 28GB
I believe it has something to do with the Indexes being rebuilt - per the Maintenance Plan the Index Rebuild is supposed to occur every Monday @ 11pm. On 6/18 it ran from 11pm - 11:44pm with no problems. Starting on 6/26 (and subsequently 7/2 and 7/9) they have started at 11 and ended anywhere between 2 - to 3 hours later. And with the following error:
Failed-1073548784) Executing the query "ALTER INDEX [PK_activityLog] ON [dbo].[activityLog] REBUILD WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, SORT_IN_TEMPDB = OFF, ONLINE = OFF ) " failed with the following error: "Could not allocate a new page for database 'FA_PROD_SDDS' because of insufficient disk space in filegroup 'PRIMARY'.
Create the necessary space by dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup. The statement has been terminated.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
The database 'FA_PROD_SDSS' is the one that I am having issues with.
Using VS2005, VB backend and javascript,
I have developed a relatively simple site - its got a few (12) simple aspx pages but its mostly client side javascript. Keeping disk storage costs down is a big concern with this my site. The disk usage for the site is ~24M. Since this was larger than I expected I started inspecting the files comprising my site and found that the "ASPNETDB.MDF" in my "App_data" folder is consuming 10.2M by itself. The thing is that site only has a few pages with calls to SQL Server - but I never did anything (that I know of) with ASPNETDB.MDF. Through VS2005, I opened up the MDF file and poked around, everything that I looked at was empty (NULL).
So my questions are:
What is causing the ASPNETDB.MDF to consume 10.2M even thought I can't see any data stored in it?
Is there anyway for my to reduce the size of this file? If so, how?
Can someone give me some pointers as to where to read up on what the ASPNETDB.MDF does?
I have do some benchmark test between the "Principle", "Mirror" and "Witness", things seems normal, the System usage (CPU, Disk Read/Write) on the "Witness" is very small and the CPU usage on Principle and Mirror is similar, However....
For the "Disk Write Bytes/sec", the usage on Principle is 200,000 and the Mirror is 1,500,000, the mirror server is talking about x7 times Disk Write Bytes/sec on the Mirror.
I cannot think of any reason why the Disk Write Bytes /sec is so much higher than the Principle, can somebody help me?
-- Initialize Control Mechanism
DECLARE@Drive TINYINT,
@SQL VARCHAR(100)
SET@Drive = 97
-- Setup Staging Area
DECLARE@Drives TABLE
(
Drive CHAR(1),
Info VARCHAR(80)
)
WHILE @Drive <= 122
BEGIN
SET@SQL = 'EXEC XP_CMDSHELL ''fsutil volume diskfree ' + CHAR(@Drive) + ':'''
INSERT@Drives
(
Info
)
EXEC(@SQL)
UPDATE@Drives
SETDrive = CHAR(@Drive)
WHEREDrive IS NULL
SET@Drive = @Drive + 1
END
-- Show the expected output
SELECTDrive,
SUM(CASE WHEN Info LIKE 'Total # of bytes : %' THEN CAST(REPLACE(SUBSTRING(Info, 32, 48), CHAR(13), '') AS BIGINT) ELSE CAST(0 AS BIGINT) END) AS TotalBytes,
SUM(CASE WHEN Info LIKE 'Total # of free bytes : %' THEN CAST(REPLACE(SUBSTRING(Info, 32, 48), CHAR(13), '') AS BIGINT) ELSE CAST(0 AS BIGINT) END) AS FreeBytes,
SUM(CASE WHEN Info LIKE 'Total # of avail free bytes : %' THEN CAST(REPLACE(SUBSTRING(Info, 32, 48), CHAR(13), '') AS BIGINT) ELSE CAST(0 AS BIGINT) END) AS AvailFreeBytes
FROM(
SELECTDrive,
Info
FROM@Drives
WHEREInfo LIKE 'Total # of %'
) AS d
GROUP BYDrive
ORDER BYDrive
E 12°55'05.25"
N 56°04'39.16"
Hi,
I have a 250 GB database and not much space left on the disk drive. I want to run SQLMAINT to do optimization and integrity checks on this db. My question is : How much work space does SQLMAINT need to perform these tasks?.
Thanks in advance for your help.
F.
How to increase disk-space of database in sql server?
View 4 Replies View Related
How do we identify the total disk space of a SQL Server Remotely. The procedure xp_fixeddrives gives the available free space .. but I would like to know the total disk capacity .. Any pointers?
Thanks,
Deepti
As a normal sql user we are not able to get the disk space information by
executing 'xp_fixeddrive' extended stored procedure.
We are able to get the result using sa user and windows authenticated user.
Any help will be appreciated.
I would be able to determine how much hard disk space is available on the Server with a SQL Query, and from an other computer on the LAN.
How can I do something?
Thanks for your help.
My SQL server disk space is getting close to full capacity which is causing certain reports that we run via the SQL server to time out because I don't think there is enough space on the server.
Any tips on cleaning out a SQL server? Are there any folders that can absolutely be deleted to clear space? I know on a local computer that the %temp% folder can be cleaned out. I know when dealing with servers you do not want to make to many changes because it can cause major problems down the road.
Hello,
We are experiencing high disk i/o on one of our RAID disk systems. Can someone tell me how I can identify the query or user or process which is causing this high disk i/o?
Thanks,
Brent.
What is the percentage of FREE disk space that is needed for a backup? I have backups that are failing with no disk space errors. But there is enough disk. Does SQL Server need a percentage of free space all the time?
Thanks
geri
I'm trying to determine how much space I would need for my data drive and log file drive to do index rebuild. I have a database which is 100gb, it is in simple recovery mode. let me know what to have a look at to determine how much space.
View 7 Replies View RelatedHello
I need to setup a compaq sever with 300 MB database, and will be adding around 600 records on a daily basis. Can someone help with how much disk space i should have on sqlserver, providing i have c: and d: setup.
Thank you in advance for all the help.
Paula
Hi All,
I have a server and it has C: D: F: I: Drives and all the system files are on C:Drive and and all the .MDF's and .LDF's(model,temp,master) are on the F: Drive and now I am running out of space on both(C: and F: Drives)
1. Can we add space to the C: and F: drives on the fly?.
2. Can I move the System databases ( MDF's and LDF's to some other drive)and if so, how do I do it?( Moving the databases ) and this is on the production database so when I have to do this.Will there be any impact.
Thanks in Advance,
Reddy
Does anyone know how to get the free disk space for all drives in many different SQL servers.
I need to populate a report (right now output in Excel) with the free disk space of all drives on all of my SQL servers.
I found xp_fixeddrives but that is specific for the server where it is executed.
Any help or pointers to a script in the archives is much appreciated.
Thanks.
I noticed something strange today. I was running a query using query analyzer on a large database (8.8 million records) and the disk space on the c: drive was dropping and eventually went to 0. Availalbe space on the c: drive is 10GB. The query did complete. SQL server and all the databases are on the d: drive. After closing the query results in query analyzer the disk space returned. Is this a concern and is there a way to change it to use the d: for whatever it is doing?
View 3 Replies View RelatedThis is my first attempt using SQL 2000 and DTS. I am importing an Access database using the DTS wizard. The process fails with a "Not enough space on temporary disk" error. There is definitely enough space on the physical disk. I don't have any limits on any folder sizes either. What "disk" is the error talking about, and how do I give it enough space. The database is relatively small, about 10MB. I believe the database was created using Access 97. Please help.
View 1 Replies View RelatedI need a way to get the used space for a physical drive, or even the total capacity for a drive to do some calculations.
I don't want to have to write my own native code either.
Is there a stored procedure that will give this to me?
I saw the xp_fixeddrives that will give the free space, but I need one of the other as well.
Any way to get this?
Thanks,
Jason
We recently moved from v6.5 to v7.0. Now I have the databases and logs set to "autogrow". How can I monitor the disk space to ensure I do not run out of room (or is that preset as to how large it can grow ?). Can't find anything in the books online. Do I do this through the NT admin tool or through the SQL*Server Enterprise Manager and more importantly - how ???
Thanks so much for any help...
Nancy
I'm trying to save a dts package and it keeps coming back with insufficient disk space. I noiticed that db MSDB was full so I manually increased the size. It was set to manually grow at 1 mb increments. But for some reason it didn't look like it was doing that so I manually increased it. Right now this is about 355 MB free so that should be plenty to save a package. But its still coming back with the same error insufficient disk space to complete operation. Any ideas on why or why it didn't grow on its own? Please help I can't seem to save any packages.
View 1 Replies View RelatedOur database -SQL Server 7.0 sp1 (NT 4.0 sp5)- is growing at a very fast rate despite the fact that we are deleting old record. It doesn't seem to be recovering disk space for the deleted records. Please let me know if there is a specific setting that can help us recover disk space. )
View 1 Replies View RelatedHi,
I'm new to these forums (and to SQL Server), so please be gentle with me.
I am developing a process to obtain information on all our remote servers/databases, and store it in a single local database.
I'm after things like db size, last backup date, free drive space etc...the usual weekly statistics.
I've linked the remote servers to my local one, and have written a few simple procedures (which exist on the local server) to grab backup and file size information from the remote tables. The output is stored locally in tables which we can then query as necessary.
I am having difficulty obtaining the free drive space details.
I'm using :-
'exec <remote_server>.master.dbo.xp_fixeddrives' to get the info, but I cannot store the output in a table on the local server. (remote_server_name, date, drive_letter, space_mb)
I wish to avoid creating any objects on the remote servers if at all possible.
I really want to pass the remote server name into the procedure, and the output to be inserted into the table.
Many thanks
is there a tsql command that returns the amount of disk space available. I want to set up a job to run and email if it gets to a certain level.
View 1 Replies View RelatedHi
I have 600 instances on my network .... I need to monitor the Disk Space ... how should I do that I mean the best way for this would be ???
By Disk Space I mean the SQL Instance should atleat have 10% of free disk space ... If it is less , maybe an alert can be sent or something of that sort .
Now it would be a pain configuring alerts on each machine.
Is there some good way to this
Thanks in advance.
All,
I think i know the answer to this one already, but would like to check before going back to my management.
Background. In the past 2 weeks, a number of our databases have shot up in size, and are now at 100% utilisation of allocated disk space. My management have asked me to look into what is causing these to fill up so quickly.
Unfortunatly there were no snap shots or information relating to the databases / tables so i can not determine which tables have grown and are causing the problems.
I have also looked through the sql logs and the event viewer logs to see if there is anything out of the ordinary, but again apart from log / database backups there is nothing of note in there.
I am going to be implementing a solution that I got off another thread which will give me some database / table history to help me in the future, but for now is there anything else i can do? or is it a case of me getting back to the application guys and getting them to reduce data (as there is no more disk space to give them).
Any thoughts or advice you can give me would be greatfully received.
Mark
-noob dba-
Okay, so i have a Dual Xeon SQL2k5 Server set up. it's got 4 10k RPM Raptor Drivers (160gb) on it all stripped together. my question is this, i was just doing all kinds of index tuning, and my server didnt have enough disk space to create 2two of the recommended adjustments. i'm curious, if i should just have those two indexes stored on the 1TB Backup array i have? (two 500gb 7200RPM drives)
or should i backup the database, and expand the Raptor Array and then restore the database and create the indexes?
i'm not really worried about down time as long as it doesnt exceed 2 days.
All,
I first ran indexdefrag on a table with 1.5 billion rows.
logical fragmentation was at 95%.
logical frag went down to 3% with no real effect on disk.
DBCC reindex had previously been bombing undetected.
Now I've run a reindex on this table:
Reindex Job with Fillfactor =100
Ran in 3:05
Free Disk went from ~150GB before operation to 49GB
File4 went from 347GB to 504GB
Why has so much free disk been consumed by this operation and not released ??????????
Is my only choice to shrink data file???
thanks
Env.
Win2k ENT os
SQL 2k5 std 64bit
one user database ldf file is growing like it reached 50GB and total D: drive space is 70GB. No SAN drives for D:. I tried to shrink the file but it not shrinking. Even I tried take to Tlog backup but it is throwing the below error
'not enough disk space'
This is production server. Please let me know how to resolve this issue?
one user database ldf file is growing like it reached 50GB and total D: drive space is 70GB. No SAN drives for D:. I tried to shrink the file but it not shrinking. Even I tried take to Tlog backup but it is throwing the below error
'not enough disk space'
This is production server. Please let me know how to resolve this issue?
I need to find out how much space is available on a disk (ex. C on the server from within a stored proc on ss2000. Is there an easy way?
Thanks!
I heard somewhere that using reporting services you are able to report on more useful aspects also such as server disk space, how is this possible, or where can i find any tutorials to help me out
thanks in advance
Jonny