check sql licence: sql

Showing posts with label sql. Show all posts

Tuesday, March 27, 2012

Clear/Purge Log files

The MDF file is 800 MB but the LDF file is 5 GB. I run the "shrink database"
but this does not seems to reduce the mdf or ldf filesize.
What I did is detach the database, delete the ldf file, re-attach the
database to create a new ldf file. If I do not do so, the application cannot
work (hang!) because the ldf file is too huge and it takes ages to commit a
transaction. Is there a "better" way to control the ldf file like
auto-purging ? Should I restrict the log file size to a specific filesize
like 500MB ? Does this mean it will auto-purge each time it reach 500MB for
the ldf file ?
ThanksThe way you manage your log file is driven by your database recovery plan.
If your recovery plan is to restore from your last full backup and not apply
transaction log backups, then change your database recovery model to SIMPLE.
This will keep your log size reasonable by removing committed transactions
from the log. The log will still need to be large enough to accommodate
your largest single transaction. If you want to reduce potential data loss,
you should use the BULK_LOGGED or FULL recovery model and backup your log
periodically.
The proper way to shrink files is with DBCC SHRINKFILE. See the Books
Online for details. You should not need to do this as part of routine
maintenance.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Carlos" <wt_know@.hotmail.com> wrote in message
news:OueiitTPEHA.620@.TK2MSFTNGP10.phx.gbl...
> The MDF file is 800 MB but the LDF file is 5 GB. I run the "shrink
database"
> but this does not seems to reduce the mdf or ldf filesize.
> What I did is detach the database, delete the ldf file, re-attach the
> database to create a new ldf file. If I do not do so, the application
cannot
> work (hang!) because the ldf file is too huge and it takes ages to commit
a
> transaction. Is there a "better" way to control the ldf file like
> auto-purging ? Should I restrict the log file size to a specific filesize
> like 500MB ? Does this mean it will auto-purge each time it reach 500MB
for
> the ldf file ?
> Thanks
>
>|||Hi,
Instead of detaching , delete the LDF file, Attach the database ytou should
have tried the below steps:-
Alter database <dbname> set single_user with rollback immediate
go
backup log <dbname> to disk='d:\backup\dbname.trn1'
go
dbcc shrinkfile('logical_log_name',truncateonly)
go
Alter database <dbname> set multi_user
After executing the above you can execute the below command check the log
file size and usage,
dbcc sqlperf(logspace)
Like Dan suggested go for SIMPLE recovery model if your data is not critical
or you not require a transaction log based recovery (POINT_IN_TIME).
Thanks
Hari
MCDBA
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:uTGdG0TPEHA.2256@.TK2MSFTNGP10.phx.gbl...
> The way you manage your log file is driven by your database recovery plan.
> If your recovery plan is to restore from your last full backup and not
apply
> transaction log backups, then change your database recovery model to
SIMPLE.
> This will keep your log size reasonable by removing committed transactions
> from the log. The log will still need to be large enough to accommodate
> your largest single transaction. If you want to reduce potential data
loss,
> you should use the BULK_LOGGED or FULL recovery model and backup your log
> periodically.
> The proper way to shrink files is with DBCC SHRINKFILE. See the Books
> Online for details. You should not need to do this as part of routine
> maintenance.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Carlos" <wt_know@.hotmail.com> wrote in message
> news:OueiitTPEHA.620@.TK2MSFTNGP10.phx.gbl...
> > The MDF file is 800 MB but the LDF file is 5 GB. I run the "shrink
> database"
> > but this does not seems to reduce the mdf or ldf filesize.
> >
> > What I did is detach the database, delete the ldf file, re-attach the
> > database to create a new ldf file. If I do not do so, the application
> cannot
> > work (hang!) because the ldf file is too huge and it takes ages to
commit
> a
> > transaction. Is there a "better" way to control the ldf file like
> > auto-purging ? Should I restrict the log file size to a specific
filesize
> > like 500MB ? Does this mean it will auto-purge each time it reach 500MB
> for
> > the ldf file ?
> >
> > Thanks
> >
> >
> >
>|||Thanks for the advices ! :-)
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:%23MWo0lUPEHA.3328@.TK2MSFTNGP09.phx.gbl...
> Hi,
> Instead of detaching , delete the LDF file, Attach the database ytou
should
> have tried the below steps:-
> Alter database <dbname> set single_user with rollback immediate
> go
> backup log <dbname> to disk='d:\backup\dbname.trn1'
> go
> dbcc shrinkfile('logical_log_name',truncateonly)
> go
> Alter database <dbname> set multi_user
> After executing the above you can execute the below command check the log
> file size and usage,
> dbcc sqlperf(logspace)
> Like Dan suggested go for SIMPLE recovery model if your data is not
critical
> or you not require a transaction log based recovery (POINT_IN_TIME).
> Thanks
> Hari
> MCDBA
>
> "Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
> news:uTGdG0TPEHA.2256@.TK2MSFTNGP10.phx.gbl...
> > The way you manage your log file is driven by your database recovery
plan.
> > If your recovery plan is to restore from your last full backup and not
> apply
> > transaction log backups, then change your database recovery model to
> SIMPLE.
> > This will keep your log size reasonable by removing committed
transactions
> > from the log. The log will still need to be large enough to accommodate
> > your largest single transaction. If you want to reduce potential data
> loss,
> > you should use the BULK_LOGGED or FULL recovery model and backup your
log
> > periodically.
> >
> > The proper way to shrink files is with DBCC SHRINKFILE. See the Books
> > Online for details. You should not need to do this as part of routine
> > maintenance.
> >
> > --
> > Hope this helps.
> >
> > Dan Guzman
> > SQL Server MVP
> >
> > "Carlos" <wt_know@.hotmail.com> wrote in message
> > news:OueiitTPEHA.620@.TK2MSFTNGP10.phx.gbl...
> > > The MDF file is 800 MB but the LDF file is 5 GB. I run the "shrink
> > database"
> > > but this does not seems to reduce the mdf or ldf filesize.
> > >
> > > What I did is detach the database, delete the ldf file, re-attach the
> > > database to create a new ldf file. If I do not do so, the application
> > cannot
> > > work (hang!) because the ldf file is too huge and it takes ages to
> commit
> > a
> > > transaction. Is there a "better" way to control the ldf file like
> > > auto-purging ? Should I restrict the log file size to a specific
> filesize
> > > like 500MB ? Does this mean it will auto-purge each time it reach
500MB
> > for
> > > the ldf file ?
> > >
> > > Thanks
> > >
> > >
> > >
> >
> >
>

Clear the transaction log

Can someone tell me the procedure the clear out the transaction log so it
doesn't crash my server again in the future?
Thanks!
JonHow did it "crash your server"?
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Jon Glazer" <jglazer.delete.me@.adconn.com> wrote in message
news:LuA2e.456$UA4.181@.fe1.columbus.rr.com...
> Can someone tell me the procedure the clear out the transaction log so it
> doesn't crash my server again in the future?
> Thanks!
> Jon
>|||Well the procedure is to back it up on a regular basis so it can reuse the
space. Or set it to Simple recovery mode if you don't want or need Log
backups. These might be of interest:
http://www.nigelrivett.net/Transact...ileGrows_1.html Log File issues
http://www.support.microsoft.com/?id=317375 Log File Grows too big
http://www.support.microsoft.com/?id=110139 Log file filling up
http://www.mssqlserver.com/faq/logs-shrinklog.asp Shrink File
http://www.support.microsoft.com/?id=315512 Considerations for Autogrow
and AutoShrink
http://www.support.microsoft.com/?id=256650 INF: How to Shrink the SQL
Server 7.0 Tran Log
http://www.support.microsoft.com/?id=272318 INF: Shrinking Log in SQL
Server 2000 with DBCC SHRINKFILE
http://www.karaszi.com/SQLServer/info_dont_shrink.asp Shrinking
considerations
Andrew J. Kelly SQL MVP
"Jon Glazer" <jglazer.delete.me@.adconn.com> wrote in message
news:LuA2e.456$UA4.181@.fe1.columbus.rr.com...
> Can someone tell me the procedure the clear out the transaction log so it
> doesn't crash my server again in the future?
> Thanks!
> Jon
>

Clear the resultset during batch processing

something like this
Select T1.issue, T2,Project
from Issue T1, Project T2...
Where T1.Rank = 'A'
-- this will return 2100 rows
Select @.ARank = @.@.rowcount
Select T1.issue, T2,Project
from Issue T1, Project T2...
Where T1.Rank = 'B'
-- this will return 1000 rows
Select @.BRank = @.@.rowcount
Select T1.issue, T2,Project
from Issue T1, Project T2...
Where T1.Rank = 'C'
-- this will return 1000 rows
Select @.CRank = @.@.rowcount
this way the code is runing in a while loop logic. I cannot use count
() function as there is data mismatch because of Duplicate records in
some table.
/soni
Falik Sher wrote:[vbcol=seagreen]
> hi,
> This means essentially you do not need these results generated by the SQLs?
> is this the case? can you send some sample SQLs so something can be
> suggested close to your needs.
> rgds
> Falik
>
> <chandresh.x.soni@.sonyericsson.com> wrote in message
> news:1164947463.012890.94570@.f1g2000cwa.googlegrou ps.com...
|||Hello Falik,
Thanks for showing a helping hand

> something like this
> Select T1.issue, T2,Project
> from Issue T1, Project T2...
> Where T1.Rank = 'A'
> -- this will return 2100 rows
> Select @.ARank = @.@.rowcount
> Select T1.issue, T2,Project
> from Issue T1, Project T2...
> Where T1.Rank = 'B'
> -- this will return 1000 rows
> Select @.BRank = @.@.rowcount
> Select T1.issue, T2,Project
> from Issue T1, Project T2...
> Where T1.Rank = 'C'
> -- this will return 1000 rows
> Select @.CRank = @.@.rowcount
> this way the code is runing in a while loop logic. I cannot use count
> () function as there is data mismatch because of Duplicate records in
> some table.
> /soni
> Falik Sher wrote:
>
sql sql

Clear the resultset during batch processing

Hello SQL Experts,
I have a Batch of Select statments which runs in a while loop and i use
the @.@.rowcount to get the rows returned to manipulate furture.
the problem is after some time in the middle of the process i get the
below error
"The query has exceeded the maximum number of result sets that can be
displayed in the results grid. Only the first 100 result sets are
displayed in the grid."
Is there a way by which i can stop the results being displayed in the
message tab or clear the diplayed result or result set as said in the
error messge. as after the error message the task stops on it own.
i dont know if there is any thing using SET statment
/sonihi,
This means essentially you do not need these results generated by the SQLs?
is this the case? can you send some sample SQLs so something can be
suggested close to your needs.
rgds
Falik
<chandresh.x.soni@.sonyericsson.com> wrote in message
news:1164947463.012890.94570@.f1g2000cwa.googlegroups.com...
> Hello SQL Experts,
> I have a Batch of Select statments which runs in a while loop and i use
> the @.@.rowcount to get the rows returned to manipulate furture.
> the problem is after some time in the middle of the process i get the
> below error
>
> "The query has exceeded the maximum number of result sets that can be
> displayed in the results grid. Only the first 100 result sets are
> displayed in the grid."
> Is there a way by which i can stop the results being displayed in the
> message tab or clear the diplayed result or result set as said in the
> error messge. as after the error message the task stops on it own.
> i dont know if there is any thing using SET statment
> /soni
>|||Hello Falik,
Thanks for showing a helping hand :)
something like this
Select T1.issue, T2,Project
from Issue T1, Project T2...
Where T1.Rank = 'A'
-- this will return 2100 rows
Select @.ARank = @.@.rowcount
Select T1.issue, T2,Project
from Issue T1, Project T2...
Where T1.Rank = 'B'
-- this will return 1000 rows
Select @.BRank = @.@.rowcount
Select T1.issue, T2,Project
from Issue T1, Project T2...
Where T1.Rank = 'C'
-- this will return 1000 rows
Select @.CRank = @.@.rowcount
this way the code is runing in a while loop logic. I cannot use count
() function as there is data mismatch because of Duplicate records in
some table.
/soni
Falik Sher wrote:
> hi,
> This means essentially you do not need these results generated by the SQLs?
> is this the case? can you send some sample SQLs so something can be
> suggested close to your needs.
> rgds
> Falik
>
> <chandresh.x.soni@.sonyericsson.com> wrote in message
> news:1164947463.012890.94570@.f1g2000cwa.googlegroups.com...
> > Hello SQL Experts,
> >
> > I have a Batch of Select statments which runs in a while loop and i use
> > the @.@.rowcount to get the rows returned to manipulate furture.
> >
> > the problem is after some time in the middle of the process i get the
> > below error
> >
> >
> > "The query has exceeded the maximum number of result sets that can be
> > displayed in the results grid. Only the first 100 result sets are
> > displayed in the grid."
> >
> > Is there a way by which i can stop the results being displayed in the
> > message tab or clear the diplayed result or result set as said in the
> > error messge. as after the error message the task stops on it own.
> >
> > i dont know if there is any thing using SET statment
> >
> > /soni
> >|||Hello Falik,
Thanks for showing a helping hand :)
something like this
Select T1.issue, T2,Project
from Issue T1, Project T2...
Where T1.Rank = 'A'
-- this will return 2100 rows
Select @.ARank = @.@.rowcount
Select T1.issue, T2,Project
from Issue T1, Project T2...
Where T1.Rank = 'B'
-- this will return 1000 rows
Select @.BRank = @.@.rowcount
Select T1.issue, T2,Project
from Issue T1, Project T2...
Where T1.Rank = 'C'
-- this will return 1000 rows
Select @.CRank = @.@.rowcount
this way the code is runing in a while loop logic. I cannot use count
() function as there is data mismatch because of Duplicate records in
some table.
/soni
Falik Sher wrote:
> hi,
> This means essentially you do not need these results generated by the SQLs?
> is this the case? can you send some sample SQLs so something can be
> suggested close to your needs.
> rgds
> Falik
>
> <chandresh.x.soni@.sonyericsson.com> wrote in message
> news:1164947463.012890.94570@.f1g2000cwa.googlegroups.com...
> > Hello SQL Experts,
> >
> > I have a Batch of Select statments which runs in a while loop and i use
> > the @.@.rowcount to get the rows returned to manipulate furture.
> >
> > the problem is after some time in the middle of the process i get the
> > below error
> >
> >
> > "The query has exceeded the maximum number of result sets that can be
> > displayed in the results grid. Only the first 100 result sets are
> > displayed in the grid."
> >
> > Is there a way by which i can stop the results being displayed in the
> > message tab or clear the diplayed result or result set as said in the
> > error messge. as after the error message the task stops on it own.
> >
> > i dont know if there is any thing using SET statment
> >
> > /soni
> >|||Hi Soni,
can u produce the sampel data , expected result and SQLs (may use the pubs
database for this), as I can;t understand the why count is not used or group
by can;t be used for this'
as down the line we need to stop the unwanted result-set generated by the
selects......
thnx
Falik
<chandresh.x.soni@.sonyericsson.com> wrote in message
news:1164955286.981884.286430@.j44g2000cwa.googlegroups.com...
> Hello Falik,
> Thanks for showing a helping hand :)
> something like this
> Select T1.issue, T2,Project
> from Issue T1, Project T2...
> Where T1.Rank = 'A'
> -- this will return 2100 rows
> Select @.ARank = @.@.rowcount
> Select T1.issue, T2,Project
> from Issue T1, Project T2...
> Where T1.Rank = 'B'
> -- this will return 1000 rows
> Select @.BRank = @.@.rowcount
> Select T1.issue, T2,Project
> from Issue T1, Project T2...
> Where T1.Rank = 'C'
> -- this will return 1000 rows
> Select @.CRank = @.@.rowcount
> this way the code is runing in a while loop logic. I cannot use count
> () function as there is data mismatch because of Duplicate records in
> some table.
> /soni
> Falik Sher wrote:
>> hi,
>> This means essentially you do not need these results generated by the
>> SQLs?
>> is this the case? can you send some sample SQLs so something can be
>> suggested close to your needs.
>> rgds
>> Falik
>>
>> <chandresh.x.soni@.sonyericsson.com> wrote in message
>> news:1164947463.012890.94570@.f1g2000cwa.googlegroups.com...
>> > Hello SQL Experts,
>> >
>> > I have a Batch of Select statments which runs in a while loop and i use
>> > the @.@.rowcount to get the rows returned to manipulate furture.
>> >
>> > the problem is after some time in the middle of the process i get the
>> > below error
>> >
>> >
>> > "The query has exceeded the maximum number of result sets that can be
>> > displayed in the results grid. Only the first 100 result sets are
>> > displayed in the grid."
>> >
>> > Is there a way by which i can stop the results being displayed in the
>> > message tab or clear the diplayed result or result set as said in the
>> > error messge. as after the error message the task stops on it own.
>> >
>> > i dont know if there is any thing using SET statment
>> >
>> > /soni
>> >
>

Clear the resultset during batch processing

something like this
Select T1.issue, T2,Project
from Issue T1, Project T2...
Where T1.Rank = 'A'
-- this will return 2100 rows
Select @.ARank = @.@.rowcount
Select T1.issue, T2,Project
from Issue T1, Project T2...
Where T1.Rank = 'B'
-- this will return 1000 rows
Select @.BRank = @.@.rowcount
Select T1.issue, T2,Project
from Issue T1, Project T2...
Where T1.Rank = 'C'
-- this will return 1000 rows
Select @.CRank = @.@.rowcount
this way the code is runing in a while loop logic. I cannot use count
() function as there is data mismatch because of Duplicate records in
some table.
/soni
Falik Sher wrote:[vbcol=seagreen]
> hi,
> This means essentially you do not need these results generated by the SQLs
?
> is this the case? can you send some sample SQLs so something can be
> suggested close to your needs.
> rgds
> Falik
>
> <chandresh.x.soni@.sonyericsson.com> wrote in message
> news:1164947463.012890.94570@.f1g2000cwa.googlegroups.com...|||Hello Falik,
Thanks for showing a helping hand

something like this
Select T1.issue, T2,Project
from Issue T1, Project T2...
Where T1.Rank = 'A'
-- this will return 2100 rows
Select @.ARank = @.@.rowcount
Select T1.issue, T2,Project
from Issue T1, Project T2...
Where T1.Rank = 'B'
-- this will return 1000 rows
Select @.BRank = @.@.rowcount
Select T1.issue, T2,Project
from Issue T1, Project T2...
Where T1.Rank = 'C'
-- this will return 1000 rows
Select @.CRank = @.@.rowcount
this way the code is runing in a while loop logic. I cannot use count
() function as there is data mismatch because of Duplicate records in
some table.
/soni
Falik Sher wrote:[vbcol=seagreen]
> hi,
> This means essentially you do not need these results generated by the SQLs
?
> is this the case? can you send some sample SQLs so something can be
> suggested close to your needs.
> rgds
> Falik
>
> <chandresh.x.soni@.sonyericsson.com> wrote in message
> news:1164947463.012890.94570@.f1g2000cwa.googlegroups.com...|||Hi Soni,
can u produce the sampel data , expected result and SQLs (may use the pubs
database for this), as I can;t understand the why count is not used or group
by can;t be used for this'
as down the line we need to stop the unwanted result-set generated by the
selects......
thnx
Falik
<chandresh.x.soni@.sonyericsson.com> wrote in message
news:1164955286.981884.286430@.j44g2000cwa.googlegroups.com...
> Hello Falik,
> Thanks for showing a helping hand

Clear SQl server Logs by run xp_cmdshell

how i can clear sql server logs by run xp_cmdshell stored procedure.
(my user in sql server is a admin).
i don't want use enterprise manager . i want write a query and use at xp_cmdshell .
befor thx.What logs are you talking about... SQLServer has error logs that you can cycle, the server keeps 5 logs active.

Or for the Transaction logs, you can use TQL to backup the transaction logs and truncate.

But do you use the Transaction logs, ie are you using them for Disaster recovery or such...
You can also turn off the transaction logs or at least turn them down. This is done buy changeing the databases logging method from Full (All Transactions Logged), to Bulk (Only bulk loading transactions), or Simple (No Logging).

There are also artilcles on SQLServercentral.com and MSDN on useing TSQL to shrink the logs and reduce wasted space.

Remember that All enterprise manager is is a front end for TSQL Commands to the database, all commands can be run from code.

clear sql error

i am using sql 2000 and calling a sp from vb.net code.
I want when a error occur in sp i want to do some processing in sp and do
not want to throw that error to front end. how can i achieve that as after
checking @.@.error and doing processing, still error gets thrown to front endHave a look at
http://www.sommarskog.se/error-handling-II.html
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"Vikram" <aa@.aa> wrote in message
news:%23iBslxSBGHA.740@.TK2MSFTNGP12.phx.gbl...
>i am using sql 2000 and calling a sp from vb.net code.
> I want when a error occur in sp i want to do some processing in sp and do
> not want to throw that error to front end. how can i achieve that as after
> checking @.@.error and doing processing, still error gets thrown to front
> end
>|||Hi
http://www.sommarskog.se/error-handling-II.html
"Vikram" <aa@.aa> wrote in message
news:%23iBslxSBGHA.740@.TK2MSFTNGP12.phx.gbl...
>i am using sql 2000 and calling a sp from vb.net code.
> I want when a error occur in sp i want to do some processing in sp and do
> not want to throw that error to front end. how can i achieve that as after
> checking @.@.error and doing processing, still error gets thrown to front
> end
>|||You can configure this in your web.config file
regards
Thakkudu
Vikram wrote:
> i am using sql 2000 and calling a sp from vb.net code.
> I want when a error occur in sp i want to do some processing in sp and do
> not want to throw that error to front end. how can i achieve that as after
> checking @.@.error and doing processing, still error gets thrown to front end[/color
]|||:)
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:O4JapKTBGHA.3488@.TK2MSFTNGP10.phx.gbl...
> Hi
> http://www.sommarskog.se/error-handling-II.html
>
>
> "Vikram" <aa@.aa> wrote in message
> news:%23iBslxSBGHA.740@.TK2MSFTNGP12.phx.gbl...
>|||There's a setting in web.config that automagically adds error-handling to
stored procedures? :)
ML
http://milambda.blogspot.com/|||Also note that when you get a chance to upgrade to SQL Server 2005 this gets
much better because SQL Server 2005 introduces TRY...CATCH for Transact-SQL,
and also the ability to retrieve the error information (not just the error
number in @.@.ERROR) in Transact-SQL.
http://msdn2.microsoft.com/en-us/library/ms179465(en-US,SQL.90).aspx
Alan Brewer [MSFT]
Content Architect, SQL Server Documentation Team
SQL Server Developer Center: http://msdn.microsoft.com/sql
SQL Server TechCenter: http://technet.microsoft.com/sql/
This posting is provided "AS IS" with no warranties, and confers no rights.|||> There's a setting in web.config that automagically adds error-handling to
> stored procedures? :)
yep:
[BestPractices]
SafeProgramming=True

Clear Old Server Names Out Of SQL Server Service Manager

How can I Clear Old Server Names Out Of SQL Server Service
Manager. They are ghosts."Jay Kusch" <Jay.Kusch@.mm-games.com> wrote in message
news:067601c3c578$b3a92520$a001280a@.phx.gbl...
> How can I Clear Old Server Names Out Of SQL Server Service
> Manager. They are ghosts.
You mean the list of servers that display for selection to connect? I do not
believe that you can remove those... If you are talking about something
different, please respond with more detail.
Steve|||This list is kept in the registry
HKEY_CURRENT_USER\Software\Microsoft\MSSQLServer\Client\PrefServers
"Jay Kusch" <Jay.Kusch@.mm-games.com> wrote in message
news:01a601c3c584$82991010$a101280a@.phx.gbl...
> >--Original Message--
> >"Jay Kusch" <Jay.Kusch@.mm-games.com> wrote in message
> >news:067601c3c578$b3a92520$a001280a@.phx.gbl...
> >> How can I Clear Old Server Names Out Of SQL Server
> Service
> >> Manager. They are ghosts.
> >
> >You mean the list of servers that display for selection
> to connect? I do not
> >believe that you can remove those... If you are talking
> about something
> >different, please respond with more detail.
> >
> >Steve
> >
> >
> >
> >
> >.
> >
> Thanks Steve ...
> That was the answer that covers it all. We have had so
> many servers in and out of test and production that my
> server list is just unmanagable. Wish there were some way
> to clear the ghosts. Thanks and Happy Holidays!
> J. Kuschsql sql

Clear log after backup

Hello everybody,
I set up a backup routine to one of my SQL-Server databases.
It´s a full backup, and I don´t know why, but the log file it´s not cleared
after the backup. I thought the full backup, when executed, would clear the
database log. Is there something missing ?
EvandroFull backups do not truncate the log. You need to issue a log backup for
that or set the recovery mode to SIMPLE.
--
Andrew J. Kelly
SQL Server MVP
"Evandro Braga" <evandro_braga@.hotmail.com> wrote in message
news:uGI4TGn9DHA.2640@.TK2MSFTNGP10.phx.gbl...
> Hello everybody,
> I set up a backup routine to one of my SQL-Server databases.
> It´s a full backup, and I don´t know why, but the log file it´s not
cleared
> after the backup. I thought the full backup, when executed, would clear
the
> database log. Is there something missing ?
>
> Evandro
>

Clear log after backup

Hello everybody,
I set up a backup routine to one of my SQL-Server databases.
Its a full backup, and I dont know why, but the log file its not cleared
after the backup. I thought the full backup, when executed, would clear the
database log. Is there something missing ?
EvandroFull backups do not truncate the log. You need to issue a log backup for
that or set the recovery mode to SIMPLE.
Andrew J. Kelly
SQL Server MVP
"Evandro Braga" <evandro_braga@.hotmail.com> wrote in message
news:uGI4TGn9DHA.2640@.TK2MSFTNGP10.phx.gbl...
> Hello everybody,
> I set up a backup routine to one of my SQL-Server databases.
> Its a full backup, and I dont know why, but the log file its not
cleared
> after the backup. I thought the full backup, when executed, would clear
the
> database log. Is there something missing ?
>
> Evandro
>

clear identical submissions

i want to clear from my db the doubles and trebles submisions and keep a unique.my db looks like:

a.a code lastname firstname phone
----------------
1 101 smith john 23452345
2 101 smith john 23452345
3 123 black mary 57645756
4 654 white peter 45634564

i want to look like

a.a code lastname firstname phone
----------------
1 101 smith john 23452345
3 123 black mary 57645756
4 654 white peter 45634564

thank you .angeloOracle 9i,

delete from table
where NOT (rowid = (select min(rowid) from table group by KEY));

KEY is defined as a candidate key that is not enforced.|||dear sir
thank you for your reply.

message returned: Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.

thank you in advance|||Select distinct columns into #temp from tableA
delete from tableA
insert into tableA select * from #temp
drop table #temp
alter table tableA add primary key (columns)|||I'd use something like:DELETE FROM table
WHERE EXISTS (SELECT *
FROM table AS b
WHERE b.id < table.id
AND b.code = table.code
AND b.lastname = table.lastname
AND b.firstname = table.firstname
AND b.phone = table.phone)

Note that I used the php tag to get a monospaced font, this is just standard SQL-92 syntax that should run on any SQL based product.

-PatP

Clear error in for each loop container

Hi,

I have a "Data Flow Task" inside" For Each container". Data flow task is processing file and updating the DB.

If one of the file is correpted i want to move to error folder and continue with the next file. i have given red arrow to a script Task which move the file to error folder. but its not continuing with next file. how can Ido that?

Any help

Set the Max Error count to 0 for the container. 0 = unlimited.|||thanks crispin.. its works.

clear DB fields at midnight

I have a dataBase named pricesDB. I would like to clear all the prices column everyday at midnight.
How should i do that?Scheduled task using either the Windows Scheduler or a SQL Agent tasksql sql

Clear data out of multiple tables.

I need to load a database with new data, from an existing parallelsystem, but the database schema has changed and I did not make a script to do the changes. Sure wish I had. So, now I would like to copy the data from the existing parallel system into the new SQL database that has the correct schema. I have built the new database from the existing changed database. Now I would like to know if there is an easy way to clear the data out of all the tables, then copy all the data from the old schema into the new schema's tables.
Thanks!
Vic(1) to create a blank DB from an existing DB
generate a full script from the existing DB and run it. it is that easy
(2) to populate data
use DTS. just a few clicks and u r done. all matching fields will be copied automatically. u can even map fields manually for non-matching fields if u feel like.
(3) reporting differences
there r tools that can compare 2 DB and generate a report of differences|||Thank you for your reply. Because I am quite new to SQL Server, I really don't know where to go to do those "few clicks." Could you please give me a little bit more direction. I'm using SQL Server 2005.
Thanks,|||sorry, i assumed that u r on SQL 2K. i do not have much knowldge about sql 2005. all that i can say is it is having options to generate scripts for sure and it does not support DTS. others might help u with details.|||Why not just drop this new database and atttach\detach or backup\restore the version you want to move? After the move you want the schema and data to be identical in both databases right? Do it in one go rather than schema then data.|||In SQL Server Management Studio:
Management >> Legacy >> Data Transformation Services.
They're fairl self explanatory...
Add connections for both datasources and then add an Transform Data Task (black arrow/cog icon). Right click your task and go to properties.
You can write a simple SQL SELECT statement in the first tab, then select it's destination in the 2nd and then map the columns in the 3rd.

Hope that's of some help. Feel free to ask away!|||I just went back and re-read my original post and I see where I need to clarify a few points.

I'm working in SQL Server 2005. Both databases are in 2005. Older SQL Servers have not been involved.
Database one (SAC) was developed, then the need for a separate, but the same database was identified (we are opening another office in another state) So, we now have added a new database, VGS.

Sort of major modification have been made to SAC, and VGS has been left alone, except data has been being added now for 6 to 8 weeks. I should have made scripts of all the modifications to SAC, but did not. I now know better! :o

So, the challenge before me is to make a copy of SAC but with VGS data in it. I know what I want, but sure don't know my way around SQL Server enough to do it without some help.

Thanks in advance for helping.|||to generate scripts for all objects, you might try this free app I wrote, using SMO. The source is available so you can tweak it if it's not exactly what you are looking for:

http://www.elsasoft.org/tools.htm

it will also script all the data out (optionally) using bcp.exe.

I am guessing you are not using source control. you should be. scriptdb.exe will help you with that because it generates a separate file for each object, which you can then check in to your favorite source control system.

Clear connections to DB

Hello,
In enterprise manager when detaching DB, there is an option to clear open
connections without actually detaching a database.
I'm looking for the sp with the ability of clearing cconnections.
I failed looking it up in books on line.
I'm using SQL 2000
I would be grateful for any help
Thanks,
GaryGary
KILL command
"Gary" <Gary@.discussions.microsoft.com> wrote in message
news:6E40C618-73B0-4BB0-9B8E-FB4A83BD5199@.microsoft.com...
> Hello,
> In enterprise manager when detaching DB, there is an option to clear open
> connections without actually detaching a database.
> I'm looking for the sp with the ability of clearing cconnections.
> I failed looking it up in books on line.
> I'm using SQL 2000
> I would be grateful for any help
> Thanks,
> Gary|||If the suggested KILL is too time-consuming as you have to kill all the
connections you can use:
ALTER DATABASE <Name> SET SINGLE_USER WITH ROLLBACK IMMEDIATE
HTH, jens Suessmeyer.|||Gary,
if you want to drop all the connections and prevent new ones:
"alter database YOURDB SET single_user with rollback immediate"
If you just want to drop all the existing connections without preventing new
ones, then you can cursor through master.dbo.sysprocesses, filter on
database and use exec to run the KILL command. If you want to go down this
route, have a go at the cursor and post it up.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)|||Hello Uri,
Kill terminates process, I need to terminate all connections to the specific
databse.
Thanks
Gary
"Uri Dimant" wrote:
> Gary
> KILL command
>
> "Gary" <Gary@.discussions.microsoft.com> wrote in message
> news:6E40C618-73B0-4BB0-9B8E-FB4A83BD5199@.microsoft.com...
> > Hello,
> >
> > In enterprise manager when detaching DB, there is an option to clear open
> > connections without actually detaching a database.
> > I'm looking for the sp with the ability of clearing cconnections.
> > I failed looking it up in books on line.
> >
> > I'm using SQL 2000
> >
> > I would be grateful for any help
> >
> > Thanks,
> > Gary
>
>|||Gary
KILL can be used to terminate a normal connection, which internally
terminates the transactions associated with the given SPID
"Gary" <Gary@.discussions.microsoft.com> wrote in message
news:B9D5EA96-C63D-452B-8082-62CFDF5CBC96@.microsoft.com...
> Hello Uri,
> Kill terminates process, I need to terminate all connections to the
> specific
> databse.
> Thanks
> Gary
> "Uri Dimant" wrote:
>> Gary
>> KILL command
>>
>> "Gary" <Gary@.discussions.microsoft.com> wrote in message
>> news:6E40C618-73B0-4BB0-9B8E-FB4A83BD5199@.microsoft.com...
>> > Hello,
>> >
>> > In enterprise manager when detaching DB, there is an option to clear
>> > open
>> > connections without actually detaching a database.
>> > I'm looking for the sp with the ability of clearing cconnections.
>> > I failed looking it up in books on line.
>> >
>> > I'm using SQL 2000
>> >
>> > I would be grateful for any help
>> >
>> > Thanks,
>> > Gary
>>

Clear connections to DB

Hello,
In enterprise manager when detaching DB, there is an option to clear open
connections without actually detaching a database.
I'm looking for the sp with the ability of clearing cconnections.
I failed looking it up in books on line.
I'm using SQL 2000
I would be grateful for any help
Thanks,
Gary
Gary
KILL command
"Gary" <Gary@.discussions.microsoft.com> wrote in message
news:6E40C618-73B0-4BB0-9B8E-FB4A83BD5199@.microsoft.com...
> Hello,
> In enterprise manager when detaching DB, there is an option to clear open
> connections without actually detaching a database.
> I'm looking for the sp with the ability of clearing cconnections.
> I failed looking it up in books on line.
> I'm using SQL 2000
> I would be grateful for any help
> Thanks,
> Gary
|||If the suggested KILL is too time-consuming as you have to kill all the
connections you can use:
ALTER DATABASE <Name> SET SINGLE_USER WITH ROLLBACK IMMEDIATE
HTH, jens Suessmeyer.
|||Gary,
if you want to drop all the connections and prevent new ones:
"alter database YOURDB SET single_user with rollback immediate"
If you just want to drop all the existing connections without preventing new
ones, then you can cursor through master.dbo.sysprocesses, filter on
database and use exec to run the KILL command. If you want to go down this
route, have a go at the cursor and post it up.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Hello Uri,
Kill terminates process, I need to terminate all connections to the specific
databse.
Thanks
Gary
"Uri Dimant" wrote:

> Gary
> KILL command
>
> "Gary" <Gary@.discussions.microsoft.com> wrote in message
> news:6E40C618-73B0-4BB0-9B8E-FB4A83BD5199@.microsoft.com...
>
>
|||Gary
KILL can be used to terminate a normal connection, which internally
terminates the transactions associated with the given SPID
"Gary" <Gary@.discussions.microsoft.com> wrote in message
news:B9D5EA96-C63D-452B-8082-62CFDF5CBC96@.microsoft.com...[vbcol=seagreen]
> Hello Uri,
> Kill terminates process, I need to terminate all connections to the
> specific
> databse.
> Thanks
> Gary
> "Uri Dimant" wrote:

Clear connections to DB

Hello,
In enterprise manager when detaching DB, there is an option to clear open
connections without actually detaching a database.
I'm looking for the sp with the ability of clearing cconnections.
I failed looking it up in books on line.
I'm using SQL 2000
I would be grateful for any help
Thanks,
GaryGary
KILL command
"Gary" <Gary@.discussions.microsoft.com> wrote in message
news:6E40C618-73B0-4BB0-9B8E-FB4A83BD5199@.microsoft.com...
> Hello,
> In enterprise manager when detaching DB, there is an option to clear open
> connections without actually detaching a database.
> I'm looking for the sp with the ability of clearing cconnections.
> I failed looking it up in books on line.
> I'm using SQL 2000
> I would be grateful for any help
> Thanks,
> Gary|||If the suggested KILL is too time-consuming as you have to kill all the
connections you can use:
ALTER DATABASE <Name> SET SINGLE_USER WITH ROLLBACK IMMEDIATE
HTH, jens Suessmeyer.|||Gary,
if you want to drop all the connections and prevent new ones:
"alter database YOURDB SET single_user with rollback immediate"
If you just want to drop all the existing connections without preventing new
ones, then you can cursor through master.dbo.sysprocesses, filter on
database and use exec to run the KILL command. If you want to go down this
route, have a go at the cursor and post it up.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)|||Hello Uri,
Kill terminates process, I need to terminate all connections to the specific
databse.
Thanks
Gary
"Uri Dimant" wrote:

> Gary
> KILL command
>
> "Gary" <Gary@.discussions.microsoft.com> wrote in message
> news:6E40C618-73B0-4BB0-9B8E-FB4A83BD5199@.microsoft.com...
>
>|||Gary
KILL can be used to terminate a normal connection, which internally
terminates the transactions associated with the given SPID
"Gary" <Gary@.discussions.microsoft.com> wrote in message
news:B9D5EA96-C63D-452B-8082-62CFDF5CBC96@.microsoft.com...[vbcol=seagreen]
> Hello Uri,
> Kill terminates process, I need to terminate all connections to the
> specific
> databse.
> Thanks
> Gary
> "Uri Dimant" wrote:
>

Clear Connections command

Scenario: You have a database that is in use and you want to detach it. You
use enterprise manager to Detach Database. A dialog box appears that lists
the Database status. It says there are 3 Connections using this database.
Next to the number of connections is a Clear button that will drop the active
connections so that you can continue the process of detaching the database.
Does anyone know the command equivalent to pressing the Clear button?
I don't want to use the graphical interface and I assume there must be some
way to issue a command that would do the same thing as pressing the Clear
button in the GUI.
I don't actually want to detach the database - I am just trying to clear all
connections other than my own. Once I finish clearing connections, I want to
issue a command to temporarily put the database into single user mode.
Alter Database FOO set SINGLE_USER with Rollback Immediate
Everything you want in one command. See the ALTER DATABASE command in BOL
for details.
To set it back to 'normal'
Alter Database FOO set MULTI_USER
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"cathedr@.wa.state.gov" <cathedrwastategov@.discussions.microsoft.com> wrote
in message news:7EA72915-DF9B-437B-9E08-AE54D210AB9C@.microsoft.com...
> Scenario: You have a database that is in use and you want to detach it.
You
> use enterprise manager to Detach Database. A dialog box appears that
lists
> the Database status. It says there are 3 Connections using this database.
> Next to the number of connections is a Clear button that will drop the
active
> connections so that you can continue the process of detaching the
database.
> Does anyone know the command equivalent to pressing the Clear button?
> I don't want to use the graphical interface and I assume there must be
some
> way to issue a command that would do the same thing as pressing the Clear
> button in the GUI.
> I don't actually want to detach the database - I am just trying to clear
all
> connections other than my own. Once I finish clearing connections, I want
to
> issue a command to temporarily put the database into single user mode.
sql sql

Clear Connections command

Scenario: You have a database that is in use and you want to detach it. You
use enterprise manager to Detach Database. A dialog box appears that lists
the Database status. It says there are 3 Connections using this database.
Next to the number of connections is a Clear button that will drop the active
connections so that you can continue the process of detaching the database.
Does anyone know the command equivalent to pressing the Clear button?
I don't want to use the graphical interface and I assume there must be some
way to issue a command that would do the same thing as pressing the Clear
button in the GUI.
I don't actually want to detach the database - I am just trying to clear all
connections other than my own. Once I finish clearing connections, I want to
issue a command to temporarily put the database into single user mode.Alter Database FOO set SINGLE_USER with Rollback Immediate
Everything you want in one command. See the ALTER DATABASE command in BOL
for details.
To set it back to 'normal'
Alter Database FOO set MULTI_USER
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"cathedr@.wa.state.gov" <cathedrwastategov@.discussions.microsoft.com> wrote
in message news:7EA72915-DF9B-437B-9E08-AE54D210AB9C@.microsoft.com...
> Scenario: You have a database that is in use and you want to detach it.
You
> use enterprise manager to Detach Database. A dialog box appears that
lists
> the Database status. It says there are 3 Connections using this database.
> Next to the number of connections is a Clear button that will drop the
active
> connections so that you can continue the process of detaching the
database.
> Does anyone know the command equivalent to pressing the Clear button?
> I don't want to use the graphical interface and I assume there must be
some
> way to issue a command that would do the same thing as pressing the Clear
> button in the GUI.
> I don't actually want to detach the database - I am just trying to clear
all
> connections other than my own. Once I finish clearing connections, I want
to
> issue a command to temporarily put the database into single user mode.

Tuesday, March 27, 2012

Clear/Purge Log files

Clear/Purge Log files

Clear the transaction log

Clear the resultset during batch processing

Clear the resultset during batch processing

Clear the resultset during batch processing

Clear SQl server Logs by run xp_cmdshell

clear sql error

Clear Old Server Names Out Of SQL Server Service Manager

Clear log after backup

Clear log after backup

clear identical submissions

Clear error in for each loop container

clear DB fields at midnight

Clear data out of multiple tables.

Clear connections to DB

Clear connections to DB

Clear connections to DB

Clear Connections command

Clear Connections command

check sql licence

Blog Archive

About Me