I would like to do an update statement to set auto shrinks to zero. A value of one means they are on. I have been using this select statement and manually changing them, but can't use it in a where clause.
select name, databasepropertyex(name, 'isautoshrink') shrink
from master.dbo.sysdatabases
I would like to do "where shrink = 1", but it won't work.
Any suggestions?
what's this : "auto shrink " db option in properties ???
in which case may i use this option ?
please help
I am using MSDE in sql server 2000.
When the database data file size exceed 2G limit, I tried to shrink the db and it
reduced by about 100MB.
2 days later, I checked the db data file size and it was reduced to 550MB, which is
1/4 of the original db size!
Please kindly explain the reason of this case, and how can it be monitored
effectively?
Thank you.
Hi,
I'm trying to get the text of all my SPs saved into text (*.sql) files. My first thought was to use sp_helptext and bcp the table to a text file but this isn't working (see my other post) so thought I'd try another method.
I can get the code from syscomment.text and concatenate the varchar(8000) field together in a text field. Unfortunately this isn't as easy as just text = text + newtext, how is this done?
Or am I doing it all comletely the wrong way? BTW, I have over 150 SPs so I can't save them individually.
Thanks!
Nick
Hi, I am trying to automate shrinking the transaction log file on SQL server.
Every so often we get errors with our application using SQL server, in which I resolve by running the backup log and shrink log commands. However, recently I got the error: Could not allocate space for object 'table_name' in database 'database_name' because the 'Primary' file group is full. To resolve this I had to create another transaction log and then run the backup log and shrink log commands.
I know need to automate the process of shrinking the log file. I have checked and the Auto Shrink checkbox is ticked but these errors still occur.
How can I delete the additional log file I created and automate this task of shrinking the log file within SQL server? Any help would be appreciated...Thanks
Hi,
I recieved an alert that says 'The Auto Shrink flag for database is not set according to best practice.' Can anyone tell me where I can find this flag and what it shoul dbe set to?
Thanks
Af.
Is Auto shrink is good option where database is very critical and there is no down time?
View 4 Replies View RelatedIs it possible/advisable to change this setting with users connected? There are a number of web based users and an agent job running every 30 seconds.
USE [master]
GO
ALTER DATABASE [Bla] SET AUTO_SHRINK OFF WITH NO_WAIT
Hello everybody,First: SQL Server 2000 sp3a, HP cluster server, MS 2003 server,database recovery model simpleTorn page detection:When I have this option turned on, processes conected with updating andwriting in database, was MUCH more slower, than if I've this optionturned off.It seams to me, that when this option is turned on, the sqlserver ismuch more slower by any operation connected with writing, than withoutthis option.This option is required for ensurance, that the data are writtenproperly on the media.My question is:if I have my database running under cluster, with RAID hd system, whichcontains write cache, with additional checking, that the data will bewritten correctly, is it necessary for me to keep this option turnedon?Auto shrink:When it is turned on, does it affect performance?Personaly, I don't think so, but somebody is insysting on it, and Iwould like to have an information from independent source about that.Best regardsMatik
View 13 Replies View RelatedIs it possible to manually force/call/start the system AUTOSHRINK process? I have an issue that appears only when the engine shrinking process is running and I need this to reproduce my bug.
I know how to start a "regular" database shrink process with:DBCC SHRINKDATABASE(xxxx);, but this is not the same as one started from the database engine.
Hi to all
Is there any option in sql server DTS or any other third party tool that can script data. By scripting data i mean that....
if a table "Employee" contains 50 rows, i want the tool to write 50 insert queries for me so that i can run in it anywhere.
Problem is i have to insert data in a remote server where i cannot use DTS. I just have a text area to write my query and press the run button..
Hope u understand my problem. In case of any explanation please reply. Waiting for your response. Thanx in advance.
by to all
Since upgrading from SQL Server Management Studio 2008 R2, I've noticed that it no longer autosaves queries that have not been manually saved first. If a file has been manually saved the autorecover files end up in the following directory:
%appdata%MicrosoftSQL Server Management Studio11.0AutoRecoverDatSolution1
However, I have ended up in the situation where I have unsaved queries when my computer has crashed and have not been able to recover them.
I have also found references to .sql files stored in temp files in the following directory, but the files here seem to be very haphazardly caught:
%userprofile%AppDataLocalTemp
If you have problems shrinking a transaction log no matter what commands you issue, here's a way to shrink the tranlog:
1. Right click on the properties of the database and go to the options tab.
2. Change the recovery mode to simple.
3. Right click again on the database - go to all tasks - shrink database.
4. Shrink the database.
5. Change the recovery mode back to what you had it as.
I found this out by trial and error as I could not find any documentation on it and no matter what I tried, I could not get the transaction log to shrink.
I have an MS SQL Server table with a Job Number field I need this field to start at a certain number then auto increment from there. Is there a way to do this programatically or within MSDE?
Thanks, Justin.
SQL 7
I am trying to run a script written for an asp page (pure vbscript) in a active script job. I am getting errors on:
Server.CreateObject("ADODB.Connection")
Yes, ADO is up to date and loaded. The error says
Error Code: 0 Error Source= Microsoft VBScript runtime error Error Description: Object required: 'Server' Error on Line 14. The step failed.
Thoughts ..
Craig
I know it's a simple question, just can't figure it out:
How do I script a new column into an existing table in SQL? I am using MS SQL 7.0 and need to create several new columns in existing tables.
thanks all.
Does anyone know how to prevent or change the
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
that appear between each Create? I can't see any options for changing then or eliminating them when generating the SQL Script for my stored procedures.
Thanks,
Scott
I have to come up a script that will be going across 60 different servers that will be looking for document type that are not being used across all servers and then deleting them.
To start with:
Select Enabled from MHGROUP.CUSTOM5
where Enabled ='N'
then delete records
and where would I run this type of script or should I say can this be done?
Thanks
Anyone know how to write a script for sql 6.5 that identifies what column is a primary key on a table? (INFORMATION_SCHEMA doesn't exist) Thanks for the help in advance.
View 10 Replies View RelatedWhat I need to do - can it be done without scripting?
1.) I need to get a list of sp's from a table (EXEC SQL task)
2.) Next, loop through the list of sp's and execute (FOREACHLOOP)
3.) Then, write the output of each sp to a uniquely named file. (DATA FLOW task)
So far, I have had success with 1 and 2. But getting the DATA FLOW task to work has been imposssible thus far.
I tried everything I can think of. I even created a child package that gets called from the FOREACHLOOP, using a Parent Variable Configuration to pass down the name of the stored procedure variable to the child package. No dice.
I am ready to throw in the towel here.
So, my question is, can what I want to do even be DONE without scripting? I was trying to avoid the "manual" approach to this project. It seems like what I want to do "should" be so simple.
In sum, I am simply trying to read a list of sp's from a table, execute the sp's, then write the output of the sp's to a uniquely named file.
Can this be done without scripting? I am beginning to think NOT.
Sigh. This is driving me crazy.
Please, any advice!!
Thanks
Hi Gurus/MVPS:
Can you please share or show me the code for scripting out stored procedures in SQL Server 2005 using SQL Cmd?. I need to perform the following:
1). Script out the text of the stored procedure
2). Output to a sql file (text file) under some directory like C:sql
3). Import the file into the destination db and run it.
The above three need to be automated in a DTS Package. I tried to use the DMO but my source server is a SQL2k5 and so DTS desginer does not let me use the ActiveX with DMO against SQL 2k5. I need to run the first step against a SQL2k5 machine and the second and third on a SQL2k machine.
Please help!!!.
Thanks
Ankith
I just starting working for a new client this week. They have a custom install program written in VB.NET which installs the database to SQL Server 2000. Normally, when I want to create a SQL script to create a database, I would go into Enterprise Manager, right-click on a database, and select All Tasks | Generate SQL Script. However, that's not how this client works. Much to my horror, the CREATE DATABASE, CREATE TABLE, etc. statements are both hard-coded and hand-coded within the install program itself. When I asked them why they weren't using SQL Server's scripting capabilities to automatically generate the script, they said that SQL Server scripting is buggy and that it doesn't work properly. It was hard getting specific details but they said it would leave out indexes, for example. Now, I'm no SQL Server expert, but I've never had a problem using SQL Server's scripting capabilities. The database they use isn't that big - maybe 20-30 tables. So, before I make an issue out of it, I was wondering what everyone else's experience with SQL Server 2000's scripting features? If you've used SQL Servers scripting to recreate a database, does it work? Did you have issues?
View 6 Replies View RelatedCan you write a script that compares table changes. I have a testing database with a bunch of table changes that i need to move to production and instead of handtypeing the changes in I was wondering if there was a method of creating a script.
View 1 Replies View RelatedHi,
Just getting into scripting sql. I have a little script that adds and drops a default value for a column. But I cannot get it to run when the column already has a default value specified. If I didn't script the contraint and specify a name I have no name to specify when trying to drop it.
ALTER TABLE users ADD CONSTRAINT dev1 DEFAULT 1 FOR ns_email_flag
ALTER TABLE users DROP CONSTRAINT dev1
How do I remove any existing default values using script?
TIA,
Seoras.
I am currently running SQL7 sp3 and will be moving the database to SQL2k. I have restored the database without any problems, but have not moved the dts packages. What would be the best solution: script the packages, save the packages to the 2k db, or just build from scratch? Any feedback would be appreciated. Thanks! -J-
View 2 Replies View RelatedI would like to move over all of my DTS packages and corresponding jobs onto another MS SQL server machine. The problem that I have is that I haven't been able to find any straight-forward info on how to script up these jobs, move them onto another machine and execute them. I have scanned through previous posts written by people with the same problem, but every response is simply "script up the jobs and move them onto another server." Maybe I'm missing something, but how "exactly" do I get a job to show up on this destination machine in EM, in the management/sql server agent/jobs folder. I did the jobs/all tasks/script job... but then what? I saved it on this destination machine, but am unclear as what to do next. The MS SQL Server Administrators companion book is of no help on this subject, and I do not understand how this simple task can be so confusing and is not documented. Any help would be greatly appreciated.
Thanks in advance!
TB
Many times here I have read about scripting DTS packages to copy them to another server. How is this done? Under "All tasks/Generate SQL Scripts" there is no option for DTS. Sould I be looking elsewhere??
View 2 Replies View RelatedI know there must be a way to script GRANTS for all users for specific objects(tables, sp's, etc). Can't find much in BOL, etc. Does anyone know how?
Any comments would be appreciated.
How can I get a script of triggers (drop & create them) without scripting the tables with them ?
Thanks!
Is there any way to script DTS Packages? :confused:
View 1 Replies View RelatedAre you kidding me?
I can't create 1 object per file anymore AND I can't have the drop created along with the create
Are they forcing you to use ALTER?
What a piece of junk
Hi all,
Is there any way to script all the triggers in a database just the way we script tables and stored procedures?
currently I am using the system tables.
regards,
Harshal.
Does anyone here know anything about the DCS Scripting Language.
A contract vacancy has arisen using this language but I've never heard of it and there does seem to be anything on the internet about it. Can anyone help?