Find Autogrowth Setting Of All Databases

Jul 1, 2015

give me query or script to find autogrowth setting of all databases in instance

View 5 Replies


ADVERTISEMENT

Autogrowth And Initial Size Setting For Database

Jul 8, 2015

give me the best autogrowth & initial size setting for database

View 7 Replies View Related

SQL 2012 :: How To Find Autogrowth OFF In DMVs

Feb 25, 2015

So I need to write a script to look for available free space percentage in my databases, but I only want it to look at capped files. We consider a file with autogrowth off as capped for our purposes.

This is my problem: in sys.database_files and sys.master_files, if I have autogrowth off then max_size is -1, which is the same value as unlimited growth.

I cannot find another setting anywhere to determine how SQL Server recognizes that a particular file is set to not allow autogrowth. Any setting in a DMV anywhere where I can see whether autogrowth is disabled or not?

View 2 Replies View Related

Setting Up Encryption For Large Databases

Jun 19, 2007

We are going to be getting a new system to house our large databases. Some of these databases will need to be encrypted. Ideally we'd like to set up encryption in SQL server rather then have to purchase an appliance to encrypt the data. What is the trade off of using SQL server 2005 to encrypt data? Does performance suffer a great deal with a large database (500g-4tb)? Also, how much more/less sure is the encryption for SQL Server? Some of our users need to be able to read the data as unencrypted while working, but we need to keep the system as secure as possible. Answers to any of these questions would be incredible helpful.



Thanks,

Robb S.

View 4 Replies View Related

How Can I Find Out "Case Sensitive Setting"

Feb 17, 2000

Hi, There
I have a BIG question.. Question is "How can I find out that SQL server7.0 has a Case Sensitive setting?" Where can I find this setting?

Thanks inadvance
Jay

View 2 Replies View Related

Moving Tables Between Databases And Setting Alias

Apr 27, 2006

Due to bad programming techniques and legacy databases, my company has a large database with all the tables in it. I would like to start moving these tables out of the database and into more appropriately named databases.I was wondering if there was a way to set an alias up so that, when the table is moved, there will be no problems with out code still trying to access the table from the old database.Maybe an example is the best way to explain this. Lets say we have a DB_Company database with table T_Customers, T_Suppliers, etc.Now, as the company grows, we decide to create a dedicated database for all the customers. So we create a database DB_Customers and move the T_Customers table into it.The problem is that they may be some code that is still trying to access the DB_Company.T_Customers table. Is there a way of setting an alias up so that, when someone tries to access the DB_Company.T_ Customers table, he is automatically redirected to the DB_Customers.T_Customers?Hope that's not too confusing :-(Jag

View 2 Replies View Related

Setting Up SQL Server Encryption Between Multiple Servers And Databases

Feb 23, 2007

Greetings.

I have a setup/deployment question regarding SQL Server Encryption.

Internal database encrypts data in 3 different tables. This could execute on any one of 6 different servers.
The tables with encrypted data are replicated to another database on different servers (3)

How should the keys/certificates be created so that the data in the replicated database can be decrypted?

In my test scenario so far, I have been unable to have the second database decrypt the data that was encrypted on the first database (currently on the same server).

-- Create Database Master Key
CREATE MASTER KEY ENCRYPTION BY
PASSWORD = 'p@ssw0rd'
GO

-- Create Certificate
CREATE CERTIFICATE MyCertificate
WITH SUBJECT = 'My Data Encryption Certificate',
EXPIRY_DATE = '10/31/2010';
GO

CREATE SYMMETRIC KEY MyKey
WITH
ALGORITHM = AES_256,
IDENTITY_VALUE = 'My Symmetric Key',
KEY_SOURCE = 'Unique phrase that will be used to secure the key'
ENCRYPTION BY CERTIFICATE MyCertificate;
GO

View 3 Replies View Related

Where To Find Detailed Error Report When Setting Up Mirroring Fails

Dec 14, 2006

I had a problem with mirroring that I only was able to resolve when I attempted to replicate instead. Replication gave me an error message that expained the problem; mirroring simply failed.

Is there any place where mirroring logs the details of what it is doing & why it is failing?

BTW, the problem was that the server name was changed after SQL was installed. Although i knew this, the forums indicated taht SQL 2005 automatically picks up the new name after it is restarted. This apparently is not true...



Thanks



Dave S

View 2 Replies View Related

How To Find No. Of Databases And Tables?

Mar 26, 2008

Hi,
is there any way to find out the no. of databases and
no. of tables in a particular database using a sql query ???

If any server variable is used then please mention that???


thanx in advance

San

View 20 Replies View Related

How To Find Out? DTS Connections To The Databases

Feb 8, 2008

I am in the process of migrating SQL Server 2000 to SQL Server 2005. Moving one user database at a time to SQL Server 2005.
Is there anyway I can list the DTS packages referenced by each databases.
All our DTS packages are stored in the database. Will the query to system or meta database will help?
Thank you,
Gish Smith

View 7 Replies View Related

How To Find Out Which Databases A User Have Access To?

Apr 3, 2007

Hi, my first question, tried to use search first but couldnät find what I need.

Hopefully someone has asuggestion.



I work for a ISV producing HR applications.

When user starts the applications the program shows all databases that the user have access to. This is done by select all names from master..sysdatabases and then trying to "USE dbname" to see if user have access,. Users doesn't have sa rights.



This procedure takes approximately 90 secs for a server with 500 databases and that has become a problem.



Does anyone know off a faster method, any suggestions?



/Regards Anders

View 1 Replies View Related

How To Set Autogrowth Off / On (T-SQL)

Oct 2, 2007

I am testing a msde 2000 sp4 database. (would like to use it against sqlserver 2005 later)
I need to turn the property for autogrowth on and check that an application alterts the user that it is on

and then off and verify the user is altered that it was set off.

I am not sure if this will work for mydb and mydblog
--turn off autogrowth
USE master
GO
ALTER DATABASE mydb
MODIFY FILE
(NAME = mydb,
FILEGROWTH = 0MB)
GO

--turn on autogrowth
USE master
GO
ALTER DATABASE mydb
MODIFY FILE
(NAME = mydb,
FILEGROWTH = 10MB)
GO

USE master
GO
ALTER DATABASE mydblog
MODIFY FILE
(NAME = mydblog,
FILEGROWTH = 0MB)
GO

--turn on autogrowth
USE master
GO
ALTER DATABASE mydblog
MODIFY FILE
(NAME = mydblog,
FILEGROWTH = 10MB)
GO


Also I need to know the t-sql for setting unrestricted growth on and off but I can not seem to find that.

Any help would be great.

We can not use enterprise manager so I'm planning to use osql thru a cmd prompt.

thanks
ced

View 2 Replies View Related

Need To Find And Document Permissions In User Databases

Feb 11, 2005

Hi folks, i've an instance with many userdatabases. i want to use SP_HELPUSER to output all database users and roles defined to em. how come i do this through a script to view permissions in all databases. I couldn't use (USE database ) in a loop.



Howdy!

View 3 Replies View Related

SQL Server 2008 :: Script To Find A Value In All Databases?

Aug 4, 2015

Script to find a value in all databases ? for example I want to find a value contain word “ SQL” in all databases so I will get the information in which database and which table it exists.

My script is only for finding a value in one database.

View 8 Replies View Related

Query To Find Databases In Full Mode

May 4, 2007

I have a stored proc that runs through my databases doing transaction logs. Currently it uses database names but this keeps being changed as a database will be added without me knowing. Next thing the transaction log job is goosed.



I really need something along the lines of select db from sys???? where mode = 'Full'



Any help appreciated.



MPM

View 1 Replies View Related

SQL Server 2008 :: Find Unused Databases Or When Last Used In A Instance?

Mar 30, 2011

Find unused databases in a instance or when last used or accessed?

I'm on SQL SERVER 2008 R2 64bit -enterprize

I need to find when the databse is last accessed.

View 3 Replies View Related

Autogrowth For MDF And LDF Files

Nov 29, 2007

I have a SQL 2005 DB that its MDF file is growing at a rate of 1 GB per day, I currently have it set up to unrestricted growth by 500 MB. Should I increase that growth to 1 GB? what would the impact of this change be? what are best practices when it comes to setting up autogrowth for MDF and LDF files?

Thanks,

Carlos

View 3 Replies View Related

What Should I Set The Autogrowth Size To?

Jul 13, 2007

Hi Guys,



Should I keep all my databases to have a standard growth size for the data and log files? What would be good practice?



Also should be be shrinking the log file every so often?



Regards,



Af

View 4 Replies View Related

Autogrowth For MDF And LDF Files

Nov 29, 2007



I have a SQL 2005 DB that its MDF file is growing at a rate of 1 GB per day, I currently have it set up to unrestricted growth by 500 MB. Should I increase that growth to 1 GB? what would the impact of this change be? what are best practices when it comes to setting up autogrowth for MDF and LDF files?


Thanks,


Carlos

View 1 Replies View Related

Autogrowth Values Keep Changing

Sep 21, 2006

Hi!

We have 2 dbs on our dw-server and the autogrowth values for both the data- and logfiles on both of these dbs changes about once a month. The data autogrowth value changes from 10megs to a percentage value between 3200 and 6400 and the log-file value changes from 10 percent to a percentage value between 3200 and 6400. Resulting in huge files and filling the drive.

What am I missing here?

BR John

View 17 Replies View Related

Can I Change The AutoGrowth Option?

Jul 13, 2007

Hi,



Is it possible to change the Autogrowth option of a database is none is set? I recieved an alert saying that one of the databases has 39.9% of freespace. Having checked the properties of the database, I noticed that the Autogrowth option had not been used.



The database is SQL 2005.



Thanks.



Af.

View 5 Replies View Related

Exponential Database Autogrowth

Dec 7, 2007

Whenever we restart the services on this one SQL server 2005 instance, the database autogrowth changes to grow by 2500%. We have to manually change the autogrowth of data file to some sane number. Has anyone faced this issue? We tried to put SP2 on but even that doesn't help. Any help would be appreciated

View 1 Replies View Related

Reporting Services :: Can't Find Any Literature On The Native Mode SSRS Databases

Jun 15, 2015

I can't find any literature on the native mode SSRS databases.

so something similar to the MSDB sys tables [URL] Is documentation non existent?

View 2 Replies View Related

How To Find Differences In Column Data Types Between Tables In Two Different Databases Using TSQL

Apr 21, 2008


I have Two Database that exist on Two seperate servers. The two database contain same schema and contains tables and columns of same name. Some tables have slight differences in terms of data types or Data type lenght.

For example if a Table on ServerA has a column named - CustomerSale with Varchar (100, Null) and a table on ServerB has a column named CustomerSale with Varchar (60, Null), how can i find if other columns have similar differences in all tables with the same name and columns in the two servers.

I am using SQL Server 2005. And the Two Servers are Linked Servers

What Script can i use to accomplish this task. Thanks






View 4 Replies View Related

Bug: Data File Autogrowth Set To 12800%

May 11, 2006

SQL2K5 SP1the autogrowth setting in one of my database's primary filegroup datafile keeps having the value of 12800%, which was originally set to100MB, everytime the service is restarted. the same occurs whenever irestore a backup of this database in our development environment.WTF? this issue does not happen to other filegroups. only on theprimary data file. whenever this happens the 4GB data file grows tomore than 70GB (even the math is incorrect) with about 95% of UNUSEDspace.has anyone else come accross this BS or anyone knows how to preventthis from happening? is MS aware of this not-so-funny joke?thanks

View 2 Replies View Related

Recovery :: How To Find Last Login Date / Time For User DML Activity On Databases On Instance

Jun 22, 2015

How to find last login date/time for user DML acitivity on databases on Instance?

Is there any way we can find our the last login date/time for databases?

Note: 1. We can find if the SQL Trace is running and store.This is not good solution
         2. Audit logins off/on is also not good solution.
         3. Using DMV's also not good option, if reboot sql server instance then historical values can not see.

View 5 Replies View Related

DB Engine :: DB Autogrowth Turned On In Secondary Data File Still 0% Internal Free Space

Oct 30, 2015

Today we received an issue on an application database on internal free space on the DB is 0% that was designed with as below

name    fileid    filename    filegroup    size    maxsize    growth    usage
XX    1    I:DataMSSQL.1MSSQLDataNew XX.mdf    PRIMARY    68140032 KB    Unlimited    0 KB    data only
XX_log    2    I:DataMSSQL.1MSSQLDataNew XX_log.LDF    NULL    1050112 KB    2147483648 KB    102400 KB    log only
XX_2    3    I:DataMSSQL.1MSSQLDataNew XX_2.ndf    PRIMARY    15458304 KB    Unlimited    0 KB    data only
XX_3    4    I:DataMSSQL.1MSSQLDataNew XX_3.ndf    PRIMARY    13186048 KB    Unlimited    0 KB    data only
XX_4    5    I:DataMSSQL.1MSSQLDataNew XX_4.ndf    PRIMARY    19570688 KB    Unlimited    204800 KB    data only
XX_5    6    I:DataMSSQL.1MSSQLDataNew XX_5.ndf    PRIMARY    19591168 KB    Unlimited    204800 KB    data only

2 of the secondary data files had its autogrowth enabled to unrestricted with 200MB and 3 of the data files including primary had its Autogowth turned OFF. Application use is complaining that there is no internal freespace on the DB.

What fails to understand us is that when the Auto growth was already TURNED OFF on 3 data files ( 1 primary and 2 secondary ) still why was the application trying to increase the space on the .mdf and .ndf files; as well when the Autogrowth is TURNED ON on 2 of the secondary data files, why was the DB not able to expand these file groups when the autogrowth is already turned off on 3 of its  other files.

What more data i need to ensure i submit an analysis to this.

View 5 Replies View Related

Linking Tables From Different Databases Or Querying From Multiple Databases

Dec 10, 2007

Dear Readers,Is it possible, like in Access, to link to tables in other SQL databases that are on the same server? I have a query that I originally had in Access that queered from multiply databases. It did this by having those other tables in the other databases linked to the database that had the query. 
 

View 3 Replies View Related

Anything That You Find In SQL Object Scripts, You Can Also Find Them In System Tables?

Jul 26, 2005

I tried all the INFORMATION_SCHEMA on SQL 2000 andI see that the system tables hold pretty much everything I aminterested in: Objects names (columns, functions, stored procedures, ...)stored procedure statements in syscomments table.My questions are:If you script your whole database everything you end up havingin the text sql scripts, are those also located in the system tables?That means i could simply read those system tables to get any informationI would normally look in the sql script files?Can i quickly generate a SQL statement of all the indexes on my database?I read many places that Microsoftsays not to modify anything in those tables and not query them since theirstructure might change in future SQL versions.Is it safe to use and rely the system tables?I basically want to do at least fetching of information i want from thesystem tables rather than the SQL script files.I also want to know if it's pretty safe for me to make changes in thesetables.Can i rename an object name for example an Index name, a Stored Procedurename?Can i add a new column in the syscolumns table for a user table?Thank you

View 4 Replies View Related

Problem: Find All Table Names In Db And Then Find

Jun 12, 2008

I have 3 database say
db1
db2
db3

I need to setup a script to read all the table names in the database above and then query the database to find the list of Stored Procedure using each table.(SQL Server)

View 5 Replies View Related

Service Broker Not Working For Restored Databases (SQL 2000 Databases Restored On 2005)

Jan 24, 2006

I just restored my SQL server 2000 database on the SQL server 2005. after this i ran the Service broker sample ("Hello World") on this database by changing the AdventureWorks name to the new database name. The "setup.sql" runs fine. When i run the "SendMessage.sql" i was not getting any rows in the output (The message was not getting inserted into the queue). I checked the Service broker is enabled on this databased using the query "select is_broker_enabled from sys.databases where name = 'newdbname' " It was 1. I even tried the ALTER DATABASE SET ENABLE_BROKER. but it didnt work.

When i tried the sample on a newly created database it worked fine.

Is there any solution to make the restored database to work for service broker.

Thanks

Prashanth

View 3 Replies View Related

Setting Timeout In ASP.NET Vs Setting Timeout In SQL Server

Oct 22, 2007

In my ASP.NET app, I'm executing a stored procedure via a SQLCommand the searches a customer database. I believe the default timeout is 90 seconds. I'm curious of what happens to the SQL Server Stored Procedure after timing out from the ASP.NET application. Does it timeout at the same time or do you have to set up a value in SQL Server?

View 1 Replies View Related

Copying Databases Between Databases

Feb 8, 2000

hi from France !!!

i would like how to duplicate a database to another server with all datas, constraints, keys, indexes...
should i use sp_attach_db, dts, backup/restore, sql scripts... ???

thanks to all, nico

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved