October 16, 2012 at 1:49 pm
That 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.
October 16, 2012 at 2:22 pm
yes. 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 1
Named Pipes Provider: Could not open a connection to SQL Server [2].
Sqlcmd: Error: Microsoft SQL Native Client : An error has occurred while establi
shing a connection to the server. When connecting to SQL Server 2005, this failu
re 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 set to ALLOW REMOTE CONNECTION.
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? thanks
October 16, 2012 at 2:40 pm
OK, 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....
October 17, 2012 at 8:34 am
Thanks 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.
October 17, 2012 at 9:04 am
You might need to specify the server as:
computername\ABCCOMPANYSERVERSQL
in the -S parameter.
October 22, 2012 at 8:18 am
Hi 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 1
Could 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 2
Could 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..
October 22, 2012 at 8:24 am
What 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.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 22, 2012 at 9:15 am
to 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 )
October 22, 2012 at 9:20 am
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_LOG
GO
USE DBNAME
GO
DBCC SHRINKFILE (FILEID, 128, TRUNCATEONLY)
GO
however, i am not sure with the FILEID where to get this.
Thanks
October 22, 2012 at 9:20 am
And 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 listed
Why 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.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 22, 2012 at 9:24 am
guzman_anthony (10/22/2012)
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_LOG
GO
USE DBNAME
GO
DBCC SHRINKFILE (FILEID, 128, TRUNCATEONLY)
GO
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 Managing Transaction Logs[/url]
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 22, 2012 at 9:32 am
Hi 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 Gail
October 22, 2012 at 9:35 am
Get more drive space or use a backup tool that compresses backups
Shrinking 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)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 22, 2012 at 9:37 am
Your 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 databasename
DBCC('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.
http://www.sqlservercentral.com/articles/Administration/64582/
October 22, 2012 at 10:06 am
Thanks 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.
Viewing 15 posts - 16 through 30 (of 31 total)
You must be logged in to reply to this topic. Login to reply