May 21, 2009 at 12:25 pm
Unlimited growth is fine, the important part is in what increments is it growing, by MB, by %?
The database file is only 580MB?? I would presume it is growing quickly too?
May 21, 2009 at 12:26 pm
How long has it taken to go from x to 3.8GB?
Are you sure this is expected volume? I would run Profiler to ge a better grasp on what is being written.
May 21, 2009 at 12:35 pm
SQLDBA (5/21/2009)
Unlimited growth is fine, the important part is in what increments is it growing, by MB, by %?The database file is only 580MB?? I would presume it is growing quickly too?
Database by 1Mb, log by 10%
Database growth is not as fast. The log file is going crazy. Now up to 4.9Gb. You can look at the timestamp on my posts to see how fast it's growing. I'd say, it's grown from 1.4 to 4.9Gb in about 4 hrs.
May 21, 2009 at 12:37 pm
If this is in Simple mode (right click the database, properties, options), then you have an open transaction that is preventing space from being reused.
Run
dbcc opentran
to find out what's open. Perhaps someone started one and didn't commit or rollback.
If it's not in simple mode, you need to run regular transaction log backups.
May 21, 2009 at 12:45 pm
Steve Jones - Editor (5/21/2009)
If this is in Simple mode (right click the database, properties, options), then you have an open transaction that is preventing space from being reused.Run
dbcc opentranto find out what's open. Perhaps someone started one and didn't commit or rollback.
If it's not in simple mode, you need to run regular transaction log backups.
Verified the database is in Simple mode.
dbcc opentran returns:
oldest nondistributed LSN: 1649:44:1
May 21, 2009 at 12:56 pm
DBCC OPENTRAN also gives u SPID. U can find that query using following
[Code]
DECLARE @HANDLE BINARY(20)
SELECT @HANDLE = sql_handle from sys.sysprocesses where spid = (yourSPID)
SELECT text FROM ::fn_get_sql(@handle)
[\code]
May 21, 2009 at 12:58 pm
DBCC OPENTRAN also gives u SPID. U can find that query using following
[Code]
DECLARE @HANDLE BINARY(20)
SELECT @HANDLE = sql_handle from sys.sysprocesses where spid = (yourSPID)
SELECT text FROM ::fn_get_sql(@handle)
[/code]
May 21, 2009 at 1:00 pm
You can use
kill (spid)
if you need to clear that connection. Once that is done, the log should not necessarily grow anymore. To find a good peak size, I'd put it in full mode, run a log backup every 15-30 minutes for a day, find the largest one, set the log to be a little larger than that and put it back in simple mode.
May 21, 2009 at 1:25 pm
LOL! Ok, I'll bite. How do I find my spid? I'm from the UNIX world and not all that familiar with Windows.
May 21, 2009 at 1:34 pm
When u run DBCC OPENTRAN, your result should be displayed as following:
Where the Bold one is ur SPID
Transaction information for database 'master'.
Oldest active transaction:
SPID (server process ID) : 52
UID (user ID) : -1
Name : user_transaction
LSN : (518:1576:1)
Start time : Jun 1 2004 3:30:07:197PM
SID : 0x010500000000000515000000a065cf7e784b9b5fe77c87709e611500
May 21, 2009 at 1:39 pm
Mayank Khatri (5/21/2009)
When u run DBCC OPENTRAN, your result should be displayed as following:Where the Bold one is ur SPID
Transaction information for database 'master'.
Oldest active transaction:
SPID (server process ID) : 52
UID (user ID) : -1
Name : user_transaction
LSN : (518:1576:1)
Start time : Jun 1 2004 3:30:07:197PM
SID : 0x010500000000000515000000a065cf7e784b9b5fe77c87709e611500
Hmmm...says none active.
May 21, 2009 at 1:47 pm
If it returns no open transactions, then either it's clear, or you have other issues.
Can you reboot the server?
May 21, 2009 at 1:48 pm
It means your transaction is no longer active now. What is the size of your Log file now? Can u post the results of Following two queries too?
[Code]
DBCC SQLPERF(LogSpace) --To see what percentage of ur Log is full
[/Code]
May 21, 2009 at 1:55 pm
Mayank Khatri (5/21/2009)
It means your transaction is no longer active now. What is the size of your Log file now? Can u post the results of Following two queries too?[Code]
DBCC SQLPERF(LogSpace) --To see what percentage of ur Log is full
[/Code]
Sitting at 6.8Gb now.
logspace for that db is at 91%
size is 6686.242mb
May 21, 2009 at 1:58 pm
That is really high number. It means the transaction that was active at that time when u ran DBCC OPENTRAN is taking up around 7 GB of log space when it runs. If that transactio runs later on today or tomorrow, it will again need that log space.
Viewing 15 posts - 16 through 30 (of 37 total)
You must be logged in to reply to this topic. Login to reply