InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 18 Jun 2013 15:45:07 GMT20http://www.sqlservercentral.com/Forums/Topic1372965-146-1.aspxYou'd be wise to listen to Gail on this. If the problem is space on the backup drive, get more space or move some of the older backups to another drive. Messing with the database to get more backup space is almost NEVER a good idea. Every action you take on a database or it's associated log has some ramification to the smooth operation of the database. Truncating the log and shrinking things without knowing how that is going to impact recoverability and performance will, sooner or later, put you in a bad situation that you may or may not be able to recover from.Mon, 22 Oct 2012 10:42:12 GMTDavid Webb-CDShttp://www.sqlservercentral.com/Forums/Topic1372965-146-1.aspx[quote][b]guzman_anthony (10/22/2012)[/b][hr]2. Run this script possible once a week?[/quote]No. As I said earlier, that script is harmful to your database and is NOT going to reduce the size of your backups in any way. So if you run that weekly you will be breaking the log chain, causing performance problems for no gain at all.[quote]Question. the EMPTYFILE command does it mean its emptying the LOGS?[/quote]No, as I said earlier it applies to data files only and is for when you are trying to drop a data file.Please go and read that article I referenced.Mon, 22 Oct 2012 10:16:49 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1372965-146-1.aspxThanks Gail and David,Ok i understand your point,however i have to relay this properly to my supervisor.Possible solution:1. is to have additional GB of backup disk. or since we have about 25days of backup file we could just delete watever is old?2. Run this script possible once a week?Question. the EMPTYFILE command does it mean its emptying the LOGS?Again, I am .001% knowledge in SQL.Thanks.Mon, 22 Oct 2012 10:06:25 GMTguzman_anthonyhttp://www.sqlservercentral.com/Forums/Topic1372965-146-1.aspxYour connection puts you into the master database by default. It looks like the files you are attempting to work with are in another database. Your connection needs to point to the dataabse where these files exist. Your script would need to change to USE databasenameDBCC('logical log file name', EMPTYFILE)to place you into the correct database before the DBCC command was issued. databasename is the name of the database where the files exist (looks like that might be ABC) and 'logical log file name' is the name of the log file in that database.As Gail has pointed out, performing this kind of operation on log files on a regular basis is not recommended and may actually be detrimental. She has an execllent article on transaction log management and you should read and understand it before you pursue this any further.[url]http://www.sqlservercentral.com/articles/Administration/64582/[/url]Mon, 22 Oct 2012 09:37:12 GMTDavid Webb-CDShttp://www.sqlservercentral.com/Forums/Topic1372965-146-1.aspxGet more drive space or use a backup tool that compresses backupsShrinking a database (data files or log files) will not in any way reduce the size of your backups.The script you were considering running would have left you unable to restore to point-in-time had you run it, potentially resulting in huge data loss in a disaster (and potential loss of jobs)Mon, 22 Oct 2012 09:35:58 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1372965-146-1.aspxHi Gail,we aim to free some space in our backup drive. again i didn't come out with this script and the task was given to me to find solution to get the script working in our BackupAssist Software.We do have this backup where we keep the all backup files/logs for atleast 25days or less.So,In order to satisfy our needs what will be your suggestion.Thanks GailMon, 22 Oct 2012 09:32:36 GMTguzman_anthonyhttp://www.sqlservercentral.com/Forums/Topic1372965-146-1.aspx[quote][b]guzman_anthony (10/22/2012)[/b][hr]Hi David,can you check this script in replacement for the previous script.. i have read this in other forum and i guess it works for them...BACKUP LOG DBNAME WITH NO_LOGGOUSE DBNAMEGODBCC SHRINKFILE (FILEID, 128, TRUNCATEONLY)GO[/quote]No, no, no! Do not run that on databases that you care about. That's about the worse log mismanagement possible.Please, please read through this and stop trying to truncate and shrink logs [url=http://www.sqlservercentral.com/articles/64582/]Managing Transaction Logs[/url]Mon, 22 Oct 2012 09:24:42 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1372965-146-1.aspxAnd do the two files that you've listed there exist in the database this is runnign from (master if you didn't specify an DB) ? If not, you'll get exactly the error that you listedWhy are you shrinking logs anyway. Regularly shrinking a log is a very bad idea. It is not going to help backups or any other process, it is going to harm your database (the log just has to regrow and probably fragments when doing so)Also note that EmptyFile is valid for data files only, not log files, and is only used when you are trying to remove a data file.Honestly, I would suggest just remove the entire log shrinking step from that batch file.Mon, 22 Oct 2012 09:20:36 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1372965-146-1.aspxHi David,can you check this script in replacement for the previous script.. i have read this in other forum and i guess it works for them...BACKUP LOG DBNAME WITH NO_LOGGOUSE DBNAMEGODBCC SHRINKFILE (FILEID, 128, TRUNCATEONLY)GOhowever, i am not sure with the FILEID where to get this.ThanksMon, 22 Oct 2012 09:20:12 GMTguzman_anthonyhttp://www.sqlservercentral.com/Forums/Topic1372965-146-1.aspxto make it easy for you to understand what i am trying to do.we have this script in batch file to run in BackupAssist program. (the script should work in dos command in order to run on the BackupAssist software.below is the script.DBCC Shrinkfile('CCGA_CAS_log',EMPTYFILE )DBCC Shrinkfile('Logging_log',EMPTYFILE )Mon, 22 Oct 2012 09:15:19 GMTguzman_anthonyhttp://www.sqlservercentral.com/Forums/Topic1372965-146-1.aspxWhat is the exact command you're trying to run there? What's in the .sql file?Looks like you're trying to issue a shrink command against a log file that does not exist. Fix the name, and note that shrinking logs (or databases) on a regular basis is a very bad thing to do.Mon, 22 Oct 2012 08:24:39 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1372965-146-1.aspxHi David, i have included the database name in the -S parameters, here's the new error...what should i consider next?Msg 8985, Level 16, State 1, Server computername\ABCCOMPANYSQLSERVER, Line 1Could not locate file 'ABC_CAS_log' for database 'master' in sys.database_files. The file either does not exist, or was dropped. Msg 8985, Level 16, State 1, Server computername\ABCCOMPANYSQLSERVER, Line 2Could not locate file 'ABCLogging_log' for database 'master' in sys.database_files. The file either does not exist, or was dropped. thanks for your help..Mon, 22 Oct 2012 08:18:51 GMTguzman_anthonyhttp://www.sqlservercentral.com/Forums/Topic1372965-146-1.aspxYou might need to specify the server as:computername\ABCCOMPANYSERVERSQL in the -S parameter.Wed, 17 Oct 2012 09:04:59 GMTDavid Webb-CDShttp://www.sqlservercentral.com/Forums/Topic1372965-146-1.aspxThanks David.I've checked the Named Pipes and is ENABLED.Want to make sure for this > Server Name = Computer Name?And, you said - I need to have the DBA? how or can you elaborate further?For, Default Instance or a Named Instance im not sure. I've checked SQL Server Configuration and i saw the name is was of our company looks like this ABCCOMPANYSERVERSQL. from there can you tell is a named instance..Appreciated your time.Wed, 17 Oct 2012 08:34:37 GMTguzman_anthonyhttp://www.sqlservercentral.com/Forums/Topic1372965-146-1.aspxOK, so it looks like it's trying to get a named pipes connection. You need to have the dba make sure that the named pipes protocol is enabled, using SQL Server Configuration Manager. You also need to make sure that the server name you are using is correct. Is it a default instance or a named instance (when it was installed, was it given a specific name?).You're close....Tue, 16 Oct 2012 14:40:00 GMTDavid Webb-CDShttp://www.sqlservercentral.com/Forums/Topic1372965-146-1.aspxyes. i forgot to specify the location of the filename..i can call the dbcc.bat now with output.txt. this one WORKS OK NOW!!!!but i still get the error below even if i am calling the script on the actual SQL Server. HResult 0x2, Level 16, State 1Named Pipes Provider: Could not open a connection to SQL Server [2].Sqlcmd: Error: Microsoft SQL Native Client : An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections..Sqlcmd: Error: Microsoft SQL Native Client : Login timeout expired.I did check the remote connection and it is [u]set to ALLOW REMOTE CONNECTION.[u]There is not point on setting up the firewall to accept the REMOTE Connection if I am calling the command on the actual SQL server? am i right?any input? thanksTue, 16 Oct 2012 14:22:04 GMTguzman_anthonyhttp://www.sqlservercentral.com/Forums/Topic1372965-146-1.aspxThat would indicate that you used the name of the batch file with the -i parameter rather than the name of the file that had the 2 DBCC commands in it (call it dbcc.sql). Since I can't really see what you're doing, that's just a guess.Tue, 16 Oct 2012 13:49:50 GMTDavid Webb-CDShttp://www.sqlservercentral.com/Forums/Topic1372965-146-1.aspxHi,i received this error message.sqlcmd :'dbcc.bat': Invalid filenamethanksTue, 16 Oct 2012 12:50:11 GMTguzman_anthonyhttp://www.sqlservercentral.com/Forums/Topic1372965-146-1.aspxAlso since you have not specified a server, that will attempt to connect to the default instance on the machine you are running it on. If that's not the correct server, you need to use -S to specify the correct server.Tue, 16 Oct 2012 11:07:55 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1372965-146-1.aspxYes, the server should be set to allow remote connections. The -E will work if the id of the login you are running is set up as a windows authenticated login inside the server. You need the -U and -P if the id you are using to run the .bat file is set up as a SQL authenticated id inside the server. If neither is true, you need to have your DBA set up the id as one or the other before you can actually make the connection.Tue, 16 Oct 2012 11:05:58 GMTDavid Webb-CDShttp://www.sqlservercentral.com/Forums/Topic1372965-146-1.aspxNo, the .bat file should only have the SQLCMD line in it. The actual DBCC commands should be in another file indicated by the -i filename on the SQLCMD command line.Tue, 16 Oct 2012 10:59:19 GMTDavid Webb-CDShttp://www.sqlservercentral.com/Forums/Topic1372965-146-1.aspxthanks David,correct me if i'm wrong. I have a batch file dbcc.bat (will .bat ok? as prerequisite to call in our third party software) with the DBCC script mentioned above will the following addition to the script. the dbcc.bat script now should look like this:-------------------------------------SQLservername -E -idbcc.sql -ooutput.txtDBCC Shrinkfile('CCGA_CAS_log',EMPTYFILE )DBCC Shrinkfile('Logging_log',EMPTYFILE )-----------------------------------i have tried this one, but i got the error message:HResult 0x2, Level 16, State 1Named Pipes Provider: Could not open a connection to SQL Server [2].Sqlcmd: Error: Microsoft SQL Native Client : An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections..Sqlcmd: Error: Microsoft SQL Native Client : Login timeout expired.****** [u]SHOULD I JUST ENABLE SQL SERVER TO ALLOW REMOTE CONNECTIONS????[/u]ThanksTue, 16 Oct 2012 10:49:11 GMTguzman_anthonyhttp://www.sqlservercentral.com/Forums/Topic1372965-146-1.aspxSQL Server is a service that runs like any other service under the OS. In order to issue commands or SQL statements to the server, you have to connect to the server and send those commands or statements to the server to execute them on your behalf. You can get a connection to the running SQL Server service in several ways. You can connect with SQL Server Management Studio, a graphical user interface for administration and development. You can use several command line utilities, like OSQL or SQLCMD that can be executed as OS level commands but which can take in script files with SQL statements and execute then in 'batch' mode. So in your case (based on what I've seen so far), you'd create a text file with the DBCC commands in it (let's call it dbcc.sql) and then issue something like the following at the OS command prompt (or in a batch file):SQLCMD -Sservername -Uuserid -Ppassword -idbcc.sql -ooutput.txtThis is basic syntax and there are several options ( you might use -E and remove the -U and -P if you are using windows authentication) that you need to choose. The above syntax will read in the dbcc.sql and execute those at the server and write out the results to the output.txt file for later examination.Tue, 16 Oct 2012 10:15:39 GMTDavid Webb-CDShttp://www.sqlservercentral.com/Forums/Topic1372965-146-1.aspxI have tried the link you provided earlier and installed the package but can get the sqlcmd running.anyway thanks for your help.Tue, 16 Oct 2012 10:10:24 GMTguzman_anthonyhttp://www.sqlservercentral.com/Forums/Topic1372965-146-1.aspxLook up SQLCMD. It's a command-line (runs from OS command console) SQL Server querying tool (runs commands against whatever SQL Server it connects to)Tue, 16 Oct 2012 10:03:54 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1372965-146-1.aspxHi David,thanks for the feedback.i do have a question for you, you said that I need to execute the DBCC command like i am executing sql through a connection to the server,can you give me an example on how i can achieved this?again thank you..Tue, 16 Oct 2012 09:59:38 GMTguzman_anthonyhttp://www.sqlservercentral.com/Forums/Topic1372965-146-1.aspxLook up SQLCMD, a utility that can run as a DOS command and read your script as input, connect to the SQL Server and execute your script. Here's a link:[url]http://msdn.microsoft.com/en-us/library/ms162773.aspx[/url]Tue, 16 Oct 2012 09:15:16 GMTDavid Webb-CDShttp://www.sqlservercentral.com/Forums/Topic1372965-146-1.aspxThanks Gail,again sorry for the ignorance. HOw should my script execute that query? Given the script.. how should my make it run in DOS command as we need this script to run in DOS command for test before we can place it to BackupAssist software.DBCC Shrinkfile('CCGA_CAS_log',EMPTYFILE )DBCC Shrinkfile('Logging_log',EMPTYFILE )please give us a hint.I appreciate your comment.Tue, 16 Oct 2012 09:07:26 GMTguzman_anthonyhttp://www.sqlservercentral.com/Forums/Topic1372965-146-1.aspxYou execute DBCC commands exactly as you execute any database query (select, update, insert, delete, etc). The error suggests you're trying to run them from the OS command prompt, that won't work, they're SQL commands, not OS commandsTue, 16 Oct 2012 08:26:38 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1372965-146-1.aspxi am a sql newbie. i will appreciate if you can provide more details in your reply.thanks david.Tue, 16 Oct 2012 08:16:43 GMTguzman_anthonyhttp://www.sqlservercentral.com/Forums/Topic1372965-146-1.aspxHow are you executing this? DBCC is a command that SQL Server recognizes but the OS does not. You need to execute the DBCCs like you would execute any other SQL, through a connection to the server.Mon, 15 Oct 2012 15:44:48 GMTDavid Webb-CDShttp://www.sqlservercentral.com/Forums/Topic1372965-146-1.aspxHi,Can anyone tell me what am i missing if i tried to test my script on the command prompt and gives me this error'DBCC' is not recognized as an internal or external command.Below is my scriptDBCC Shrinkfile('ABC_CAS_log',EMPTYFILE )DBCC Shrinkfile('ABCLogging_log',EMPTYFILE )any help is greatly appreciatedthanks,aguzmanMon, 15 Oct 2012 15:25:03 GMTguzman_anthony