MDF Files currupted: log

Showing posts with label log. Show all posts

Friday, March 23, 2012

Name of running procedure

Is it possible in 2000 (or 2005) to get the name of the currently running
procedure, from inside the procedure?
I would like to write code to log procedure events, without having to change
the procedure name as I copy it from one procedure to another.
Thanks,
JayOn Tue, 18 Sep 2007 10:59:51 -0700, "Jay" <nospan@.nospam.org> wrote:
>Is it possible in 2000 (or 2005) to get the name of the currently running
>procedure, from inside the procedure?
Yes.
CREATE PROCEDURE testprocedure AS
SELECT @.@.PROCID AS 'ProcID',
OBJECT_NAME(@.@.PROCID) AS 'Procedure'
GO
EXEC testprocedure
GO
Roy Harvey
Beacon Falls, CT|||Thanks Roy.
"Roy Harvey (MVP)" <roy_harvey@.snet.net> wrote in message
news:t260f39s14nj0ph8k4iofg7i6furlusfi7@.4ax.com...
> On Tue, 18 Sep 2007 10:59:51 -0700, "Jay" <nospan@.nospam.org> wrote:
>>Is it possible in 2000 (or 2005) to get the name of the currently running
>>procedure, from inside the procedure?
> Yes.
> CREATE PROCEDURE testprocedure AS
> SELECT @.@.PROCID AS 'ProcID',
> OBJECT_NAME(@.@.PROCID) AS 'Procedure'
> GO
> EXEC testprocedure
> GO
> Roy Harvey
> Beacon Falls, CT

Monday, March 19, 2012

Mysterious login error message

I get the following error messages in the sql server error log

Source Logon

Message
Error: 18456, Severity: 14, State: 11.

and

Source Logon

Message
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. [CLIENT: 185.23.11.33]

The scenario is: We set up log-shipping (LS) between a clustered sql server system (source server) and a stand-alone sql server box (target server). (SQL Server 2K5 EE + SP1), and LS goes very well, but on the target server, we found the above-mentioned error messages.
BTW: the two servers are in the same domain.

Did I miss something in configuration?

Thanks in advance for your help..

Jeff

It looks like a delegation problem. Most likely the credentials from a 3rd machine (most likely the client) are used to connect to the source SQL Server are being used to connect to the target SQL Server. If this is the case, the following articles will hopefully help you to fix it:

Security Account Delegation http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_security_2gmm.asp

Specifying Credential and Connection Information http://msdn2.microsoft.com/en-us/library/ms160330.aspx

Let us know if this information helped.

-Raul Garcia

SDE/T

SQL Server Engine

|||

Hi Raul,

I do not think we have a 3rd machine/application to connect to the source server / or the target server. Actually, we setup both source server and target server (in our log-shipping scenario) to use the same domain account as SQL Server service and sql server agent service startup account.

For the 1st reference link you mentioned, i.e. Security Account Delegation http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_security_2gmm.asp our environment is Win 2K3 EE + SP1 (64 bit) and SQL Server 2K5 EE + SP1 (64bit), so it seems unapplicable here.

Personally I believe it is has something to do with the log-shipping, because we setup log shipping cycle at 10 minutes (from 12:00:00 am to 11:59:00pm), and I can see every 10 minutes, for example at 12:10:00am, the login failure errors occur in the target server's sql server error log, and then not appear until 12:20:00am.

Do you have some other thoughts regarding this?

Thanks a lot for your help, which is greatly appreciated...

BTW, in the error message

Message
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. [CLIENT: 185.23.11.33]
Here [CLIENT: 185.23.11.33] is the ip address of our source server (node 1 in two clustered server environment)

|||

Unfortunately I cannot think of any other reason why you would get this particular error message; hopefully somebody else may have a better answer. Another option could be to post your question on the SQL Server Database Engine forum (http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=93&SiteID=1), hopefully there we may find somebody with more knowledge on log-shipping who may be able to answer your question.

-Raul Garcia

SDE/T

SQL Server Engine

|||With the help from MVP oj, I finally figured it out. The error appeared because a linked server (on the source) was created by the Log-shipping wizard ( but I still do not understand why Log-shipping needs a linked server), and after configured the linked server by giving a remote user with password, the error disappears.

Friday, March 9, 2012

mysql installation problem in Xp

hello !

I am not able to install Mysql on my pc runnig XP
..the log file shows server is readyfor connection on port 3306.

but still client not able to server .
error comes can't connect localhaost to server on port 10061

winmysqladmin showing green indiacting server is running but server
and lient info are not there.

pls help.

smita[posted and mailed]

Smita (smitap56@.rediffmail.com) writes:
> I am not able to install Mysql on my pc runnig XP
> .the log file shows server is readyfor connection on port 3306.
> but still client not able to server .
> error comes can't connect localhaost to server on port 10061
> winmysqladmin showing green indiacting server is running but server
> and lient info are not there.

Please try http://www.mysql.com. I believe they have some support forum
there. In this group we discsuss Microsoft SQL Server, so we can't help
you.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Wednesday, March 7, 2012

My Transaction Log size very strange

Hi
I use SQL Server 2000
I use Full recuperation mode.
My DB size is 458 MB (344 in use, 114 available)
My log size is 18 MB
I do Full backup every hour starting at 8:00 am until 21:00
I do transaction backup every 10 minutes.
The problem is that the size of the transaction log backup is strange, as
follows:
Time Size(KB) filename
...
05:52 78.336 myDB_tlog_200507140552.TRN
06:02 78.336 myDB_tlog_200507140602.TRN
06:12 78.336 myDB_tlog_200507140612.TRN
06:22 78.336 myDB_tlog_200507140622.TRN
06:32 11.776 myDB_tlog_200507140632.TRN
06:42 11.776 myDB_tlog_200507140642.TRN
06:52 78.336 myDB_tlog_200507140652.TRN
07:03 359.117.312 myDB_tlog_200507140703.TRN Note
07:12 133.072.384 myDB_tlog_200507140712.TRN Note
07:22 291.328 myDB_tlog_200507140722.TRN
07:32 291.328 myDB_tlog_200507140732.TRN
07:42 291.328 myDB_tlog_200507140742.TRN
07:52 356.864 myDB_tlog_200507140752.TRN
08:02 356.864 myDB_tlog_200507140802.TRN
08:12 356.864 myDB_tlog_200507140812.TRN
08:22 357.888 myDB_tlog_200507140822.TRN
08:32 422.400 myDB_tlog_200507140832.TRN
08:42 10.663.424 myDB_tlog_200507140842.TRN Note
08:52 437.760 myDB_tlog_200507140852.TRN
09:02 437.760 myDB_tlog_200507140902.TRN
09:12 699.904 myDB_tlog_200507140912.TRN
09:22 503.296 myDB_tlog_200507140922.TRN
09:32 568.832 myDB_tlog_200507140932.TRN
09:42 568.832 myDB_tlog_200507140942.TRN
09:52 569.856 myDB_tlog_200507140952.TRN
10:02 372.224 myDB_tlog_200507141002.TRN
10:12 10.442.240 myDB_tlog_200507141012.TRN Note
10:22 413.184 myDB_tlog_200507141022.TRN
10:32 545.280 myDB_tlog_200507141032.TRN
10:42 479.744 myDB_tlog_200507141042.TRN
10:52 414.208 myDB_tlog_200507141052.TRN
11:02 479.744 myDB_tlog_200507141102.TRN
11:12 413.184 myDB_tlog_200507141112.TRN
11:22 414.208 myDB_tlog_200507141122.TRN
11:32 546.304 myDB_tlog_200507141132.TRN
11:42 1.093.120 myDB_tlog_200507141142.TRN
11:52 303.616 myDB_tlog_200507141152.TRN
12:02 436.736 myDB_tlog_200507141202.TRN
12:12 369.152 myDB_tlog_200507141212.TRN
12:22 239.104 myDB_tlog_200507141222.TRN
12:32 172.544 myDB_tlog_200507141232.TRN
12:42 107.008 myDB_tlog_200507141242.TRN
12:52 172.544 myDB_tlog_200507141252.TRN
13:02 239.104 myDB_tlog_200507141302.TRN
13:12 9.269.760 myDB_tlog_200507141312.TRN Note
13:22 158.208 myDB_tlog_200507141322.TRN
13:32 158.208 myDB_tlog_200507141332.TRN
13:42 92.672 myDB_tlog_200507141342.TRN
13:52 158.208 myDB_tlog_200507141352.TRN
14:02 92.672 myDB_tlog_200507141402.TRN
14:12 92.672 myDB_tlog_200507141412.TRN
14:22 92.672 myDB_tlog_200507141422.TRN
14:32 289.280 myDB_tlog_200507141432.TRN
14:42 16.926.208 myDB_tlog_200507141442.TRN Note
14:52 280.064 myDB_tlog_200507141452.TRN
15:02 411.136 myDB_tlog_200507141502.TRN
Any ideas?
I want to know what is happening, and how to fix it.
Thanks in advance.Hi,
The Size of the transaction log backup will be bigger if you have Bulk
transaction or if you are doing a maintanence operation such
as UPDATE STATISTICS, DBCC INDEXDEFREAG or DBCC DBREINDEX.
Looking in to the current size the transaction log (LDF) I feel that you are
in safe side.
Thanks
Hari
SQL Server MVP
"Juan Carlos" <jcgalarzar@.hotmail.com> wrote in message
news:eP2z25KiFHA.320@.TK2MSFTNGP09.phx.gbl...
> Hi
> I use SQL Server 2000
> I use Full recuperation mode.
> My DB size is 458 MB (344 in use, 114 available)
> My log size is 18 MB
> I do Full backup every hour starting at 8:00 am until 21:00
> I do transaction backup every 10 minutes.
> The problem is that the size of the transaction log backup is strange, as
> follows:
> Time Size(KB) filename
> ...
> 05:52 78.336 myDB_tlog_200507140552.TRN
> 06:02 78.336 myDB_tlog_200507140602.TRN
> 06:12 78.336 myDB_tlog_200507140612.TRN
> 06:22 78.336 myDB_tlog_200507140622.TRN
> 06:32 11.776 myDB_tlog_200507140632.TRN
> 06:42 11.776 myDB_tlog_200507140642.TRN
> 06:52 78.336 myDB_tlog_200507140652.TRN
> 07:03 359.117.312 myDB_tlog_200507140703.TRN Note
> 07:12 133.072.384 myDB_tlog_200507140712.TRN Note
> 07:22 291.328 myDB_tlog_200507140722.TRN
> 07:32 291.328 myDB_tlog_200507140732.TRN
> 07:42 291.328 myDB_tlog_200507140742.TRN
> 07:52 356.864 myDB_tlog_200507140752.TRN
> 08:02 356.864 myDB_tlog_200507140802.TRN
> 08:12 356.864 myDB_tlog_200507140812.TRN
> 08:22 357.888 myDB_tlog_200507140822.TRN
> 08:32 422.400 myDB_tlog_200507140832.TRN
> 08:42 10.663.424 myDB_tlog_200507140842.TRN Note
> 08:52 437.760 myDB_tlog_200507140852.TRN
> 09:02 437.760 myDB_tlog_200507140902.TRN
> 09:12 699.904 myDB_tlog_200507140912.TRN
> 09:22 503.296 myDB_tlog_200507140922.TRN
> 09:32 568.832 myDB_tlog_200507140932.TRN
> 09:42 568.832 myDB_tlog_200507140942.TRN
> 09:52 569.856 myDB_tlog_200507140952.TRN
> 10:02 372.224 myDB_tlog_200507141002.TRN
> 10:12 10.442.240 myDB_tlog_200507141012.TRN Note
> 10:22 413.184 myDB_tlog_200507141022.TRN
> 10:32 545.280 myDB_tlog_200507141032.TRN
> 10:42 479.744 myDB_tlog_200507141042.TRN
> 10:52 414.208 myDB_tlog_200507141052.TRN
> 11:02 479.744 myDB_tlog_200507141102.TRN
> 11:12 413.184 myDB_tlog_200507141112.TRN
> 11:22 414.208 myDB_tlog_200507141122.TRN
> 11:32 546.304 myDB_tlog_200507141132.TRN
> 11:42 1.093.120 myDB_tlog_200507141142.TRN
> 11:52 303.616 myDB_tlog_200507141152.TRN
> 12:02 436.736 myDB_tlog_200507141202.TRN
> 12:12 369.152 myDB_tlog_200507141212.TRN
> 12:22 239.104 myDB_tlog_200507141222.TRN
> 12:32 172.544 myDB_tlog_200507141232.TRN
> 12:42 107.008 myDB_tlog_200507141242.TRN
> 12:52 172.544 myDB_tlog_200507141252.TRN
> 13:02 239.104 myDB_tlog_200507141302.TRN
> 13:12 9.269.760 myDB_tlog_200507141312.TRN Note
> 13:22 158.208 myDB_tlog_200507141322.TRN
> 13:32 158.208 myDB_tlog_200507141332.TRN
> 13:42 92.672 myDB_tlog_200507141342.TRN
> 13:52 158.208 myDB_tlog_200507141352.TRN
> 14:02 92.672 myDB_tlog_200507141402.TRN
> 14:12 92.672 myDB_tlog_200507141412.TRN
> 14:22 92.672 myDB_tlog_200507141422.TRN
> 14:32 289.280 myDB_tlog_200507141432.TRN
> 14:42 16.926.208 myDB_tlog_200507141442.TRN Note
> 14:52 280.064 myDB_tlog_200507141452.TRN
> 15:02 411.136 myDB_tlog_200507141502.TRN
> Any ideas?
> I want to know what is happening, and how to fix it.
> Thanks in advance.
>
>|||Thanks Hari.
But I'm still confused about then changing size!!!
From 300 KB to 16 MB.
Any ideas?
Thanks in advance.
"Hari Prasad" <hari_prasad_k@.hotmail.com> escribió en el mensaje
news:uLHvBRLiFHA.2852@.TK2MSFTNGP15.phx.gbl...
> Hi,
> The Size of the transaction log backup will be bigger if you have Bulk
> transaction or if you are doing a maintanence operation such
> as UPDATE STATISTICS, DBCC INDEXDEFREAG or DBCC DBREINDEX.
> Looking in to the current size the transaction log (LDF) I feel that you
are
> in safe side.
> Thanks
> Hari
> SQL Server MVP
>
>
> "Juan Carlos" <jcgalarzar@.hotmail.com> wrote in message
> news:eP2z25KiFHA.320@.TK2MSFTNGP09.phx.gbl...
> > Hi
> > I use SQL Server 2000
> > I use Full recuperation mode.
> > My DB size is 458 MB (344 in use, 114 available)
> > My log size is 18 MB
> > I do Full backup every hour starting at 8:00 am until 21:00
> > I do transaction backup every 10 minutes.
> >
> > The problem is that the size of the transaction log backup is strange,
as
> > follows:
> >
> > Time Size(KB) filename
> > ...
> > 05:52 78.336 myDB_tlog_200507140552.TRN
> > 06:02 78.336 myDB_tlog_200507140602.TRN
> > 06:12 78.336 myDB_tlog_200507140612.TRN
> > 06:22 78.336 myDB_tlog_200507140622.TRN
> > 06:32 11.776 myDB_tlog_200507140632.TRN
> > 06:42 11.776 myDB_tlog_200507140642.TRN
> > 06:52 78.336 myDB_tlog_200507140652.TRN
> > 07:03 359.117.312 myDB_tlog_200507140703.TRN Note
> > 07:12 133.072.384 myDB_tlog_200507140712.TRN Note
> > 07:22 291.328 myDB_tlog_200507140722.TRN
> > 07:32 291.328 myDB_tlog_200507140732.TRN
> > 07:42 291.328 myDB_tlog_200507140742.TRN
> > 07:52 356.864 myDB_tlog_200507140752.TRN
> > 08:02 356.864 myDB_tlog_200507140802.TRN
> > 08:12 356.864 myDB_tlog_200507140812.TRN
> > 08:22 357.888 myDB_tlog_200507140822.TRN
> > 08:32 422.400 myDB_tlog_200507140832.TRN
> > 08:42 10.663.424 myDB_tlog_200507140842.TRN Note
> > 08:52 437.760 myDB_tlog_200507140852.TRN
> > 09:02 437.760 myDB_tlog_200507140902.TRN
> > 09:12 699.904 myDB_tlog_200507140912.TRN
> > 09:22 503.296 myDB_tlog_200507140922.TRN
> > 09:32 568.832 myDB_tlog_200507140932.TRN
> > 09:42 568.832 myDB_tlog_200507140942.TRN
> > 09:52 569.856 myDB_tlog_200507140952.TRN
> > 10:02 372.224 myDB_tlog_200507141002.TRN
> > 10:12 10.442.240 myDB_tlog_200507141012.TRN Note
> > 10:22 413.184 myDB_tlog_200507141022.TRN
> > 10:32 545.280 myDB_tlog_200507141032.TRN
> > 10:42 479.744 myDB_tlog_200507141042.TRN
> > 10:52 414.208 myDB_tlog_200507141052.TRN
> > 11:02 479.744 myDB_tlog_200507141102.TRN
> > 11:12 413.184 myDB_tlog_200507141112.TRN
> > 11:22 414.208 myDB_tlog_200507141122.TRN
> > 11:32 546.304 myDB_tlog_200507141132.TRN
> > 11:42 1.093.120 myDB_tlog_200507141142.TRN
> > 11:52 303.616 myDB_tlog_200507141152.TRN
> > 12:02 436.736 myDB_tlog_200507141202.TRN
> > 12:12 369.152 myDB_tlog_200507141212.TRN
> > 12:22 239.104 myDB_tlog_200507141222.TRN
> > 12:32 172.544 myDB_tlog_200507141232.TRN
> > 12:42 107.008 myDB_tlog_200507141242.TRN
> > 12:52 172.544 myDB_tlog_200507141252.TRN
> > 13:02 239.104 myDB_tlog_200507141302.TRN
> > 13:12 9.269.760 myDB_tlog_200507141312.TRN Note
> > 13:22 158.208 myDB_tlog_200507141322.TRN
> > 13:32 158.208 myDB_tlog_200507141332.TRN
> > 13:42 92.672 myDB_tlog_200507141342.TRN
> > 13:52 158.208 myDB_tlog_200507141352.TRN
> > 14:02 92.672 myDB_tlog_200507141402.TRN
> > 14:12 92.672 myDB_tlog_200507141412.TRN
> > 14:22 92.672 myDB_tlog_200507141422.TRN
> > 14:32 289.280 myDB_tlog_200507141432.TRN
> > 14:42 16.926.208 myDB_tlog_200507141442.TRN Note
> > 14:52 280.064 myDB_tlog_200507141452.TRN
> > 15:02 411.136 myDB_tlog_200507141502.TRN
> >
> > Any ideas?
> > I want to know what is happening, and how to fix it.
> > Thanks in advance.
> >
> >
> >
>|||The amount of data in the log is totally dependant on what you are doing
since the last log backup. Use profiler to see what is happening during the
time when the log backups are large and you will see what i causing the
difference.
--
Andrew J. Kelly SQL MVP
"Juan Carlos" <jcgalarzar@.hotmail.com> wrote in message
news:%23pEhNpLiFHA.1372@.TK2MSFTNGP10.phx.gbl...
> Thanks Hari.
> But I'm still confused about then changing size!!!
> From 300 KB to 16 MB.
> Any ideas?
> Thanks in advance.
> "Hari Prasad" <hari_prasad_k@.hotmail.com> escribió en el mensaje
> news:uLHvBRLiFHA.2852@.TK2MSFTNGP15.phx.gbl...
>> Hi,
>> The Size of the transaction log backup will be bigger if you have Bulk
>> transaction or if you are doing a maintanence operation such
>> as UPDATE STATISTICS, DBCC INDEXDEFREAG or DBCC DBREINDEX.
>> Looking in to the current size the transaction log (LDF) I feel that you
> are
>> in safe side.
>> Thanks
>> Hari
>> SQL Server MVP
>>
>>
>> "Juan Carlos" <jcgalarzar@.hotmail.com> wrote in message
>> news:eP2z25KiFHA.320@.TK2MSFTNGP09.phx.gbl...
>> > Hi
>> > I use SQL Server 2000
>> > I use Full recuperation mode.
>> > My DB size is 458 MB (344 in use, 114 available)
>> > My log size is 18 MB
>> > I do Full backup every hour starting at 8:00 am until 21:00
>> > I do transaction backup every 10 minutes.
>> >
>> > The problem is that the size of the transaction log backup is strange,
> as
>> > follows:
>> >
>> > Time Size(KB) filename
>> > ...
>> > 05:52 78.336 myDB_tlog_200507140552.TRN
>> > 06:02 78.336 myDB_tlog_200507140602.TRN
>> > 06:12 78.336 myDB_tlog_200507140612.TRN
>> > 06:22 78.336 myDB_tlog_200507140622.TRN
>> > 06:32 11.776 myDB_tlog_200507140632.TRN
>> > 06:42 11.776 myDB_tlog_200507140642.TRN
>> > 06:52 78.336 myDB_tlog_200507140652.TRN
>> > 07:03 359.117.312 myDB_tlog_200507140703.TRN Note
>> > 07:12 133.072.384 myDB_tlog_200507140712.TRN Note
>> > 07:22 291.328 myDB_tlog_200507140722.TRN
>> > 07:32 291.328 myDB_tlog_200507140732.TRN
>> > 07:42 291.328 myDB_tlog_200507140742.TRN
>> > 07:52 356.864 myDB_tlog_200507140752.TRN
>> > 08:02 356.864 myDB_tlog_200507140802.TRN
>> > 08:12 356.864 myDB_tlog_200507140812.TRN
>> > 08:22 357.888 myDB_tlog_200507140822.TRN
>> > 08:32 422.400 myDB_tlog_200507140832.TRN
>> > 08:42 10.663.424 myDB_tlog_200507140842.TRN Note
>> > 08:52 437.760 myDB_tlog_200507140852.TRN
>> > 09:02 437.760 myDB_tlog_200507140902.TRN
>> > 09:12 699.904 myDB_tlog_200507140912.TRN
>> > 09:22 503.296 myDB_tlog_200507140922.TRN
>> > 09:32 568.832 myDB_tlog_200507140932.TRN
>> > 09:42 568.832 myDB_tlog_200507140942.TRN
>> > 09:52 569.856 myDB_tlog_200507140952.TRN
>> > 10:02 372.224 myDB_tlog_200507141002.TRN
>> > 10:12 10.442.240 myDB_tlog_200507141012.TRN Note
>> > 10:22 413.184 myDB_tlog_200507141022.TRN
>> > 10:32 545.280 myDB_tlog_200507141032.TRN
>> > 10:42 479.744 myDB_tlog_200507141042.TRN
>> > 10:52 414.208 myDB_tlog_200507141052.TRN
>> > 11:02 479.744 myDB_tlog_200507141102.TRN
>> > 11:12 413.184 myDB_tlog_200507141112.TRN
>> > 11:22 414.208 myDB_tlog_200507141122.TRN
>> > 11:32 546.304 myDB_tlog_200507141132.TRN
>> > 11:42 1.093.120 myDB_tlog_200507141142.TRN
>> > 11:52 303.616 myDB_tlog_200507141152.TRN
>> > 12:02 436.736 myDB_tlog_200507141202.TRN
>> > 12:12 369.152 myDB_tlog_200507141212.TRN
>> > 12:22 239.104 myDB_tlog_200507141222.TRN
>> > 12:32 172.544 myDB_tlog_200507141232.TRN
>> > 12:42 107.008 myDB_tlog_200507141242.TRN
>> > 12:52 172.544 myDB_tlog_200507141252.TRN
>> > 13:02 239.104 myDB_tlog_200507141302.TRN
>> > 13:12 9.269.760 myDB_tlog_200507141312.TRN Note
>> > 13:22 158.208 myDB_tlog_200507141322.TRN
>> > 13:32 158.208 myDB_tlog_200507141332.TRN
>> > 13:42 92.672 myDB_tlog_200507141342.TRN
>> > 13:52 158.208 myDB_tlog_200507141352.TRN
>> > 14:02 92.672 myDB_tlog_200507141402.TRN
>> > 14:12 92.672 myDB_tlog_200507141412.TRN
>> > 14:22 92.672 myDB_tlog_200507141422.TRN
>> > 14:32 289.280 myDB_tlog_200507141432.TRN
>> > 14:42 16.926.208 myDB_tlog_200507141442.TRN Note
>> > 14:52 280.064 myDB_tlog_200507141452.TRN
>> > 15:02 411.136 myDB_tlog_200507141502.TRN
>> >
>> > Any ideas?
>> > I want to know what is happening, and how to fix it.
>> > Thanks in advance.
>> >
>> >
>> >
>>
>

My Transaction Log size very strange

my sqlsp.log

this is the end of my sqlsp.log after sp3a install to existing SQL 200
11:03:54 Begin: SetupRegistry(
11:03:54 Register this installation of SQL Server with MDA
11:03:54 Completed registering this installation of SQL Server with MDA
11:03:55 Start Process...updating reg entries for S
11:03:55 No Server -> Tool
11:03:55 End: SetupRegistry(
11:03:55 Begin: PostSetupRegistry(
11:03:55 C:\PROGRA~1\COMMON~1\MICROS~1\SQLDEB~1\SQLDBR~1.EXE sqldbreg2.exe -RegServe
11:03:56 End: PostSetupRegistry(
11:04:07 You should now backup your master and msdb databases since this installation has updated their content
11:04:07 Action CleanUpInstall
11:04:09 Installation Completed with Errors (number of nonfatal errors: 1
But when I check @.@.version I am still on sp2. Did not see indication of what the nonfatal error was. Idea what I should be looking for?The last line says: 11:04:09 Installation Completed with
Errors (number of nonfatal errors: 1)
Your SQL SP# installation was not succesfull, please fix
the issue and re-install em
Thank you,
Saleem Hakani
>--Original Message--
>this is the end of my sqlsp.log after sp3a install to
existing SQL 2000
>11:03:54 Begin: SetupRegistry()
>11:03:54 Register this installation of SQL Server with
MDAC
>11:03:54 Completed registering this installation of SQL
Server with MDAC
>11:03:55 Start Process...updating reg entries for SP
>11:03:55 No Server -> Tools
>11:03:55 End: SetupRegistry()
>11:03:55 Begin: PostSetupRegistry()
>11:03:55 C:\PROGRA~1\COMMON~1\MICROS~1\SQLDEB~1
\SQLDBR~1.EXE sqldbreg2.exe -RegServer
>11:03:56 End: PostSetupRegistry()
>11:04:07 You should now backup your master and msdb
databases since this installation has updated their
content.
>11:04:07 Action CleanUpInstall:
>11:04:09 Installation Completed with Errors (number of
nonfatal errors: 1)
>But when I check @.@.version I am still on sp2. Did not
see indication of what the nonfatal error was. Idea what
I should be looking for?
>.
>

Monday, February 20, 2012

my Log file size is 10GB....

SQL log file size on 22/06/2007 shown as 10GB...now its showing as 20GB...but when taking backup...the backup file size is 4MB only.....when restoring, it showing msg that there is no space, due to the file size of 10 GB.... any body can tell the solution for this?.....plz urgenthi,
run the following commands.

1.dbcc sqlperf(logspace) . in the out put u can get the log size.
2.backup log <dbname> with truncate_only
3.dbcc shrinkdatabase(dbname,10)
4.dbcc shrinkfile(2) (2 means log file)

after these steps over execute step 1 again after 10 or 20 mins ,let me know the log size growth.
good luck
Bharat|||

Quote:

Originally Posted by bharadwaj

hi,
run the following commands.

1.dbcc sqlperf(logspace) . in the out put u can get the log size.
2.backup log <dbname> with truncate_only
3.dbcc shrinkdatabase(dbname,10)
4.dbcc shrinkfile(2) (2 means log file)

after these steps over execute step 1 again after 10 or 20 mins ,let me know the log size growth.
good luck
Bharat

=============

Hi Bharadwaj.
Thank you for your quickest solution. I will try and get back to you.

Good day.
Alagar

My log file is growing huge! Why? Help ASAP! please

Since upgrading to 2005 from 2000, in my sql event log, for 2005, I am
seeing this multiple times each day:
---
Autogrow of file 'MyDatabase_Log' in database 'MyDatabase' was cancelled by
user or timed out after __ milliseconds. Use ALTER DATABASE to set a smaller
FileGrowth value for this file or to explicitly set a new file size.
---
(file growth = 10%)
At the same time, applications are experiencing timeouts on reads/writes,
which is a major problem, probably (I'm guessing) because this is causing a
database lock. This particular DB is about 3 GB and the log file is about
30 GB. The log file was only 1 mb before I did the upgrade. I have no
idea why this is happening. I am not using replication or log shipping or
anything like that.
a) what gets stored in the log file?
b) why is mine growing out of control?
c) how do I stop the growth?
d) how do I get the log file back to a small size again?Do a backup of the transaction log and then shrink the file.
Ben Nevarez, MCDBA, OCP
Database Administrator
"D. Patrick" wrote:
> Since upgrading to 2005 from 2000, in my sql event log, for 2005, I am
> seeing this multiple times each day:
> ---
> Autogrow of file 'MyDatabase_Log' in database 'MyDatabase' was cancelled by
> user or timed out after __ milliseconds. Use ALTER DATABASE to set a smaller
> FileGrowth value for this file or to explicitly set a new file size.
> ---
> (file growth = 10%)
> At the same time, applications are experiencing timeouts on reads/writes,
> which is a major problem, probably (I'm guessing) because this is causing a
> database lock. This particular DB is about 3 GB and the log file is about
> 30 GB. The log file was only 1 mb before I did the upgrade. I have no
> idea why this is happening. I am not using replication or log shipping or
> anything like that.
> a) what gets stored in the log file?
> b) why is mine growing out of control?
> c) how do I stop the growth?
> d) how do I get the log file back to a small size again?
>
>|||Thanks for the reply, but I don't believe that solves the underlying
problem. Why is it growing? What should look at?
And BOL says that shrinking the file may not actually shrink the physical
file. I am very confused and would appreciate any information or
step-by-step instructions. Thank you.
"Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
news:C306E3FF-132D-4E8C-A150-078DB9A2DB2D@.microsoft.com...
> Do a backup of the transaction log and then shrink the file.
> Ben Nevarez, MCDBA, OCP
> Database Administrator
>
> "D. Patrick" wrote:
>> Since upgrading to 2005 from 2000, in my sql event log, for 2005, I am
>> seeing this multiple times each day:
>> ---
>> Autogrow of file 'MyDatabase_Log' in database 'MyDatabase' was cancelled
>> by
>> user or timed out after __ milliseconds. Use ALTER DATABASE to set a
>> smaller
>> FileGrowth value for this file or to explicitly set a new file size.
>> ---
>> (file growth = 10%)
>> At the same time, applications are experiencing timeouts on reads/writes,
>> which is a major problem, probably (I'm guessing) because this is causing
>> a
>> database lock. This particular DB is about 3 GB and the log file is
>> about
>> 30 GB. The log file was only 1 mb before I did the upgrade. I have no
>> idea why this is happening. I am not using replication or log shipping
>> or
>> anything like that.
>> a) what gets stored in the log file?
>> b) why is mine growing out of control?
>> c) how do I stop the growth?
>> d) how do I get the log file back to a small size again?
>>|||A reason for the growth is that your DB is perhaps in Fully logged
recovery mode and maybe it was in simple recovery mode on the old
server
Take a look at this it might help you out
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||That is true. I changed that option recently. Does that option really use
10x the space of the database (and more, it seems, by the day)? It seems
that it is useless if it uses 3 GB per day on a 3 GB database. Is there
some other setting that removes log info after each database backup, so that
it will only keep a bit of log data?
Can I change that back to simple while the production DB is running?
I have read BOL and your URL links about shrinking database files. Maybe
I'm just stupid, but I still have no idea what to type into the query window
to get back to a nice little 1 MB log file (instead of the 30 GB and growing
it is at right now). Please help. Thanks.
"SQL" <denis.gobo@.gmail.com> wrote in message
news:1147461310.310058.75290@.g10g2000cwb.googlegroups.com...
>A reason for the growth is that your DB is perhaps in Fully logged
> recovery mode and maybe it was in simple recovery mode on the old
> server
> Take a look at this it might help you out
> http://www.karaszi.com/SQLServer/info_dont_shrink.asp
>
> Denis the SQL Menace
> http://sqlservercode.blogspot.com/
>|||It is normal for the transaction log file to grow. Are you doing regular
transaction log backups?
Try that first (backup and shrink) and let us know what happen.
Ben Nevarez, MCDBA, OCP
Database Administrator
"D. Patrick" wrote:
> Thanks for the reply, but I don't believe that solves the underlying
> problem. Why is it growing? What should look at?
> And BOL says that shrinking the file may not actually shrink the physical
> file. I am very confused and would appreciate any information or
> step-by-step instructions. Thank you.
>
> "Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
> news:C306E3FF-132D-4E8C-A150-078DB9A2DB2D@.microsoft.com...
> >
> > Do a backup of the transaction log and then shrink the file.
> >
> > Ben Nevarez, MCDBA, OCP
> > Database Administrator
> >
> >
> > "D. Patrick" wrote:
> >
> >> Since upgrading to 2005 from 2000, in my sql event log, for 2005, I am
> >> seeing this multiple times each day:
> >>
> >> ---
> >> Autogrow of file 'MyDatabase_Log' in database 'MyDatabase' was cancelled
> >> by
> >> user or timed out after __ milliseconds. Use ALTER DATABASE to set a
> >> smaller
> >> FileGrowth value for this file or to explicitly set a new file size.
> >> ---
> >> (file growth = 10%)
> >>
> >> At the same time, applications are experiencing timeouts on reads/writes,
> >> which is a major problem, probably (I'm guessing) because this is causing
> >> a
> >> database lock. This particular DB is about 3 GB and the log file is
> >> about
> >> 30 GB. The log file was only 1 mb before I did the upgrade. I have no
> >> idea why this is happening. I am not using replication or log shipping
> >> or
> >> anything like that.
> >>
> >> a) what gets stored in the log file?
> >> b) why is mine growing out of control?
> >> c) how do I stop the growth?
> >> d) how do I get the log file back to a small size again?
> >>
> >>
> >>
>
>|||I have not been backing up the log. Is that the problem? When you do a log
backup, does that automatically then drop the file back to 1 MB again? A
command example that I can type in the query window would greatly help me.
"Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
news:1CAE345F-A36B-4840-B190-495705186E06@.microsoft.com...
> It is normal for the transaction log file to grow. Are you doing regular
> transaction log backups?
> Try that first (backup and shrink) and let us know what happen.
> Ben Nevarez, MCDBA, OCP
> Database Administrator
>
> "D. Patrick" wrote:
>> Thanks for the reply, but I don't believe that solves the underlying
>> problem. Why is it growing? What should look at?
>> And BOL says that shrinking the file may not actually shrink the physical
>> file. I am very confused and would appreciate any information or
>> step-by-step instructions. Thank you.
>>
>> "Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
>> news:C306E3FF-132D-4E8C-A150-078DB9A2DB2D@.microsoft.com...
>> >
>> > Do a backup of the transaction log and then shrink the file.
>> >
>> > Ben Nevarez, MCDBA, OCP
>> > Database Administrator
>> >
>> >
>> > "D. Patrick" wrote:
>> >
>> >> Since upgrading to 2005 from 2000, in my sql event log, for 2005, I am
>> >> seeing this multiple times each day:
>> >>
>> >> ---
>> >> Autogrow of file 'MyDatabase_Log' in database 'MyDatabase' was
>> >> cancelled
>> >> by
>> >> user or timed out after __ milliseconds. Use ALTER DATABASE to set a
>> >> smaller
>> >> FileGrowth value for this file or to explicitly set a new file size.
>> >> ---
>> >> (file growth = 10%)
>> >>
>> >> At the same time, applications are experiencing timeouts on
>> >> reads/writes,
>> >> which is a major problem, probably (I'm guessing) because this is
>> >> causing
>> >> a
>> >> database lock. This particular DB is about 3 GB and the log file is
>> >> about
>> >> 30 GB. The log file was only 1 mb before I did the upgrade. I have
>> >> no
>> >> idea why this is happening. I am not using replication or log
>> >> shipping
>> >> or
>> >> anything like that.
>> >>
>> >> a) what gets stored in the log file?
>> >> b) why is mine growing out of control?
>> >> c) how do I stop the growth?
>> >> d) how do I get the log file back to a small size again?
>> >>
>> >>
>> >>
>>|||backup log with TRUNCATE_ONLY will release space, however you still
need to issue a SHRINK command after that
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||Two questions:
Can I do this on the production server without causing live problems?
Should I do this in a script, in the future, after all full database backups
(assuming that I don't want to roll back prior to the full backup moment)?
"SQL" <denis.gobo@.gmail.com> wrote in message
news:1147463618.444554.51680@.i40g2000cwc.googlegroups.com...
> backup log with TRUNCATE_ONLY will release space, however you still
> need to issue a SHRINK command after that
> Denis the SQL Menace
> http://sqlservercode.blogspot.com/
>|||My database is similar in size to yours (actually a little larger). I have
SQL Agent backing up the log every 15 minutes with full logging on. This
keeps the log file from growing too large. Also, in the event of a failure,
I am able to restore to the point 15 minutes (sometimes 30) before the
failure. We (OK my boss) decided this was an acceptable trade off vs. the
expense of having a true failover server.
"D. Patrick" <replywithinthegroup@.thenotreal.com> wrote in message
news:BL59g.1916$uM4.1877@.tornado.socal.rr.com...
> Two questions:
> Can I do this on the production server without causing live problems?
> Should I do this in a script, in the future, after all full database
backups
> (assuming that I don't want to roll back prior to the full backup moment)?
>
> "SQL" <denis.gobo@.gmail.com> wrote in message
> news:1147463618.444554.51680@.i40g2000cwc.googlegroups.com...
> > backup log with TRUNCATE_ONLY will release space, however you still
> > need to issue a SHRINK command after that
> >
> > Denis the SQL Menace
> > http://sqlservercode.blogspot.com/
> >
>|||Would you mind posting the script that is running to back up your log file?
"Ron Hinds" <__ron__dontspamme@.wedontlikespam_garageiq.com> wrote in message
news:uLFGIAgdGHA.4276@.TK2MSFTNGP03.phx.gbl...
> My database is similar in size to yours (actually a little larger). I have
> SQL Agent backing up the log every 15 minutes with full logging on. This
> keeps the log file from growing too large. Also, in the event of a
> failure,
> I am able to restore to the point 15 minutes (sometimes 30) before the
> failure. We (OK my boss) decided this was an acceptable trade off vs. the
> expense of having a true failover server.
> "D. Patrick" <replywithinthegroup@.thenotreal.com> wrote in message
> news:BL59g.1916$uM4.1877@.tornado.socal.rr.com...
>> Two questions:
>> Can I do this on the production server without causing live problems?
>> Should I do this in a script, in the future, after all full database
> backups
>> (assuming that I don't want to roll back prior to the full backup
>> moment)?
>>
>> "SQL" <denis.gobo@.gmail.com> wrote in message
>> news:1147463618.444554.51680@.i40g2000cwc.googlegroups.com...
>> > backup log with TRUNCATE_ONLY will release space, however you still
>> > need to issue a SHRINK command after that
>> >
>> > Denis the SQL Menace
>> > http://sqlservercode.blogspot.com/
>> >
>>
>|||It won't make much sense. I used Enterprise Manager and created a DB
Maintenance Plan to do the job. So the script looks like:
EXECUTE master.dbo.xp_sqlmaint N'-PlanID
8797A992-C063-43C1-AAC4-5C0302531C89 -WriteHistory -VrfyBackup -BkUpMedia
DISK -BkUpLog "C:\USB_Drive\SQL_Backup" -DelBkUps 4DAYS -BkExt "TRN"'
Using a maintenance plan is pretty easy for something like this that isn't
too complicated.
"D. Patrick" <replywithinthegroup@.thenotreal.com> wrote in message
news:6369g.1950$uM4.783@.tornado.socal.rr.com...
> Would you mind posting the script that is running to back up your log
file?
>
> "Ron Hinds" <__ron__dontspamme@.wedontlikespam_garageiq.com> wrote in
message
> news:uLFGIAgdGHA.4276@.TK2MSFTNGP03.phx.gbl...
> > My database is similar in size to yours (actually a little larger). I
have
> > SQL Agent backing up the log every 15 minutes with full logging on. This
> > keeps the log file from growing too large. Also, in the event of a
> > failure,
> > I am able to restore to the point 15 minutes (sometimes 30) before the
> > failure. We (OK my boss) decided this was an acceptable trade off vs.
the
> > expense of having a true failover server.
> >
> > "D. Patrick" <replywithinthegroup@.thenotreal.com> wrote in message
> > news:BL59g.1916$uM4.1877@.tornado.socal.rr.com...
> >> Two questions:
> >>
> >> Can I do this on the production server without causing live problems?
> >>
> >> Should I do this in a script, in the future, after all full database
> > backups
> >> (assuming that I don't want to roll back prior to the full backup
> >> moment)?
> >>
> >>
> >> "SQL" <denis.gobo@.gmail.com> wrote in message
> >> news:1147463618.444554.51680@.i40g2000cwc.googlegroups.com...
> >> > backup log with TRUNCATE_ONLY will release space, however you still
> >> > need to issue a SHRINK command after that
> >> >
> >> > Denis the SQL Menace
> >> > http://sqlservercode.blogspot.com/
> >> >
> >>
> >>
> >
> >
>|||thank you.
"Ron Hinds" <__ron__dontspamme@.wedontlikespam_garageiq.com> wrote in message
news:eRHv8VgdGHA.564@.TK2MSFTNGP02.phx.gbl...
> It won't make much sense. I used Enterprise Manager and created a DB
> Maintenance Plan to do the job. So the script looks like:
> EXECUTE master.dbo.xp_sqlmaint N'-PlanID
> 8797A992-C063-43C1-AAC4-5C0302531C89 -WriteHistory -VrfyBackup -BkUpMedia
> DISK -BkUpLog "C:\USB_Drive\SQL_Backup" -DelBkUps 4DAYS -BkExt "TRN"'
> Using a maintenance plan is pretty easy for something like this that isn't
> too complicated.
> "D. Patrick" <replywithinthegroup@.thenotreal.com> wrote in message
> news:6369g.1950$uM4.783@.tornado.socal.rr.com...
>> Would you mind posting the script that is running to back up your log
> file?
>>
>> "Ron Hinds" <__ron__dontspamme@.wedontlikespam_garageiq.com> wrote in
> message
>> news:uLFGIAgdGHA.4276@.TK2MSFTNGP03.phx.gbl...
>> > My database is similar in size to yours (actually a little larger). I
> have
>> > SQL Agent backing up the log every 15 minutes with full logging on.
>> > This
>> > keeps the log file from growing too large. Also, in the event of a
>> > failure,
>> > I am able to restore to the point 15 minutes (sometimes 30) before the
>> > failure. We (OK my boss) decided this was an acceptable trade off vs.
> the
>> > expense of having a true failover server.
>> >
>> > "D. Patrick" <replywithinthegroup@.thenotreal.com> wrote in message
>> > news:BL59g.1916$uM4.1877@.tornado.socal.rr.com...
>> >> Two questions:
>> >>
>> >> Can I do this on the production server without causing live problems?
>> >>
>> >> Should I do this in a script, in the future, after all full database
>> > backups
>> >> (assuming that I don't want to roll back prior to the full backup
>> >> moment)?
>> >>
>> >>
>> >> "SQL" <denis.gobo@.gmail.com> wrote in message
>> >> news:1147463618.444554.51680@.i40g2000cwc.googlegroups.com...
>> >> > backup log with TRUNCATE_ONLY will release space, however you still
>> >> > need to issue a SHRINK command after that
>> >> >
>> >> > Denis the SQL Menace
>> >> > http://sqlservercode.blogspot.com/
>> >> >
>> >>
>> >>
>> >
>> >
>>
>|||Another way to do it is to "detach" the database and then "attach" it back.
This can be done throught Enterprise Manager. Usually one does that as part
of moving a database, but if you just reattach from the same place, it works
fine and shrinks the log file. We went from 10 GB to under 1 MB. I had
experimented with shrink and truncate before and never could get it quite
right, but the detach/attach worked fine. Alternatively, you can use Query
Analyzer and run sp_detach_db and sp_attach_db.|||"D. Patrick" <replywithinthegroup@.thenotreal.com> wrote in message
news:NB59g.1899$uM4.1049@.tornado.socal.rr.com...
> I have not been backing up the log. Is that the problem?
Yes, that is exactly the problem.
> When you do a log
> backup, does that automatically then drop the file back to 1 MB again?
Not quite, it removes all complete transactions. If you have transactions
that aren't complete, they'll stay in the log until the next backup.
> A
> command example that I can type in the query window would greatly help me.
backup log <dbname> to disk='f:\log_backups\<dbname>_tlog_200605121724.trn'
But your best bet is to schedule a daily or more often backup.
(we do ours every 20 minutes for example so that we're never more than 20
minutes "out of date" in the event of disaster recovery.)
>
> "Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
> news:1CAE345F-A36B-4840-B190-495705186E06@.microsoft.com...
> >
> > It is normal for the transaction log file to grow. Are you doing regular
> > transaction log backups?
> >
> > Try that first (backup and shrink) and let us know what happen.
> >
> > Ben Nevarez, MCDBA, OCP
> > Database Administrator
> >
> >
> > "D. Patrick" wrote:
> >
> >> Thanks for the reply, but I don't believe that solves the underlying
> >> problem. Why is it growing? What should look at?
> >>
> >> And BOL says that shrinking the file may not actually shrink the
physical
> >> file. I am very confused and would appreciate any information or
> >> step-by-step instructions. Thank you.
> >>
> >>
> >> "Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
> >> news:C306E3FF-132D-4E8C-A150-078DB9A2DB2D@.microsoft.com...
> >> >
> >> > Do a backup of the transaction log and then shrink the file.
> >> >
> >> > Ben Nevarez, MCDBA, OCP
> >> > Database Administrator
> >> >
> >> >
> >> > "D. Patrick" wrote:
> >> >
> >> >> Since upgrading to 2005 from 2000, in my sql event log, for 2005, I
am
> >> >> seeing this multiple times each day:
> >> >>
> >> >> ---
> >> >> Autogrow of file 'MyDatabase_Log' in database 'MyDatabase' was
> >> >> cancelled
> >> >> by
> >> >> user or timed out after __ milliseconds. Use ALTER DATABASE to set a
> >> >> smaller
> >> >> FileGrowth value for this file or to explicitly set a new file size.
> >> >> ---
> >> >> (file growth = 10%)
> >> >>
> >> >> At the same time, applications are experiencing timeouts on
> >> >> reads/writes,
> >> >> which is a major problem, probably (I'm guessing) because this is
> >> >> causing
> >> >> a
> >> >> database lock. This particular DB is about 3 GB and the log file is
> >> >> about
> >> >> 30 GB. The log file was only 1 mb before I did the upgrade. I
have
> >> >> no
> >> >> idea why this is happening. I am not using replication or log
> >> >> shipping
> >> >> or
> >> >> anything like that.
> >> >>
> >> >> a) what gets stored in the log file?
> >> >> b) why is mine growing out of control?
> >> >> c) how do I stop the growth?
> >> >> d) how do I get the log file back to a small size again?
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>|||"D Shirley" <D Shirley@.discussions.microsoft.com> wrote in message
news:E07A48F1-468A-407E-8FCD-885A72C51297@.microsoft.com...
> Another way to do it is to "detach" the database and then "attach" it
back.
> This can be done throught Enterprise Manager. Usually one does that as
part
> of moving a database, but if you just reattach from the same place, it
works
> fine and shrinks the log file.
Umm, this should NOT work that way unless you're deleting the logfile.
Don't do that. it may work and you could end up with a ruined database.
> We went from 10 GB to under 1 MB. I had
> experimented with shrink and truncate before and never could get it quite
> right, but the detach/attach worked fine. Alternatively, you can use
Query
> Analyzer and run sp_detach_db and sp_attach_db.
>
Also, do NOT shrink and truncate unless you really really don't care about
disaster recovery. (or have very simple needs.)
Shrinking can lead to a fragmented file on the disk and truncating means
you've limited what you can recover.

My log file is growing huge! Why? Help ASAP! please

My log file has grow the disk full

My logfile has grow the disk full - the logfil is 25 gb and I have 4 gb free.
I can't shrink the log fil !
Can I set the log file to null ??
I have backup my datafil successfully!
Help!Detach the database, and the delete the log-file manually. Re-Attach the Database, a new log file would be created.|||Thank you :-)|||Originally posted by TALAT
Detach the database, and the delete the log-file manually. Re-Attach the Database, a new log file would be created.

I get a : Error 1813: Could not open new database 'xxxx'. Create database is aborted. Device activation error. The fysical file name 'xx'
may be incorrect.

??|||Detach the DB, rename the existing Log File and then attach the database and it will prompt for a new log file, say ok and you should be all set.
Otherwise:

Backup Tran DBName with No_log
then do
use dbname
dbcc shrinkfile(logfilename,truncateonly)|||Originally posted by sqlserver2k
Detach the DB, rename the existing Log File and then attach the database and it will prompt for a new log file, say ok and you should be all set.
Otherwise:

Backup Tran DBName with No_log
then do
use dbname
dbcc shrinkfile(logfilename,truncateonly)

How can I write the path ?? "e:\logfile\logfilename" don't work ?

The SQLserver can't find the file in sysfiles!

<dbcc shrinkfile(logfilename,truncateonly)> ?|||What exactly are u doin? Restoring the DB? Check if the file name has an extension and u r missing the .tran or .bak extention in the Quotes.|||By this time you might have detached the database using SP_DETACH_DB.

Now delete the existing >LDF file from the path and use SP_ATTACH_SINGLE_FILE_DB to reattch the database which will create new log file as specified.

Books online would be the best help in this regard.|||Originally posted by Satya
By this time you might have detached the database using SP_DETACH_DB.

Now delete the existing >LDF file from the path and use SP_ATTACH_SINGLE_FILE_DB to reattch the database which will create new log file as specified.

Books online would be the best help in this regard.

Tanks for your reply :-)

EXEC sp_detach_db @.dbname = 'xxx'

EXEC sp_attach_single_file_db @.dbname = 'xxx',
@.physname = 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\xxx.mdf'|||Yes thats correct and what was the result.:)

Friday, March 23, 2012

Name of running procedure

Monday, March 19, 2012

Mysterious login error message

Friday, March 9, 2012

mysql installation problem in Xp

Wednesday, March 7, 2012

My Transaction Log size very strange

My Transaction Log size very strange

My Transaction Log size very strange

my sqlsp.log

Monday, February 20, 2012

my Log file size is 10GB....

My log file is growing huge! Why? Help ASAP! please

My log file is growing huge! Why? Help ASAP! please

My log file has grow the disk full

MDF Files currupted

Blog Archive

About Me