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
ADVERTISEMENT
Jul 1, 2015
give me query or script to find autogrowth setting of all databases in instance
View 5 Replies
View Related
Mar 27, 2008
I am wondering where the DMVs are stored? In tempdb? or resourcebd? or somewhere else?
View 5 Replies
View Related
May 9, 2007
All,
My current understanding is that most, if not all dmv's are dynamic in that if a server is rebooted than the information gathered from the dmv's will be reset. What happens if the mirror instance fails over (manually or because of a hardward/software issue)? Does the failover of the principal to the mirror node cause the a resetting of the dmvs?
Thanks,
Ian
View 1 Replies
View Related
Mar 25, 2015
Queries against DMVs like sys.dm_XXX causing blocking in user databases? I have never read that querying DMVs has caused blocking in user databases but that question recently came up in my environment.
View 5 Replies
View Related
Oct 11, 2007
Hi all
I cannot find this info anywhere. We have Hyperion 8.x running against SQL 2000. We will upgrade side-by-side in the coming weeks. It appears that Hyperion 8.x is assured to continue to work without issue with a 2K5 database in 80 compatibility level.
I have seen the BOL table on differences between 80 and 90 (specifically affects us - WITH on Index hints), but what I wonder are about 3 specific features.
1. Do you still get DMV capabilities in 80 mode? (I ran a test on sys.dm_db_index_usage_stats for an 80 compat DB, creating index and using query to hit index, and yes - it does look as if this DMV does get populated).
2. Can you still Table Partition in 80 mode? (Ran a test in DEV, and yes - this does look possible - any gotchas?)
3. Does the optimizer behave quite differently in 80 mode?
I have tested in DEV moving a DB from 80 to 90 and back, and this seems to work with no problems.
TIA from Grand Fenwick!! ;-)
View 1 Replies
View Related
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
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
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
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
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
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
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
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
Jul 8, 2015
give me the best autogrowth & initial size setting for database
View 7 Replies
View Related
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
Sep 17, 2014
I got an error that says to run DBCC CHECKTABLE, but I can't find what table it is. It gives the db id and page Id. I ran DBCC PAGE but I could not figure out what object is using that page. I posted the error and dbcc page results below. Also, Slot 74 is not missing. I could not find m_objId or AllocUnitId in any system tables . Also, I got the same error for this db several other times. When I ran dbcc checkdb I did not get any errors. This is a SQL 2012 db created by SharePoint.
Why is m_nextPage and m_prevPage = (0:0) ?
Does the m_type=3 change how a page is linked?
I found Type 3 – text mix page. A text page that holds small chunks of LOB values plus internal parts of text tree. These can be shared between LOB values in the same partition of an index or heap.
Error2 from log--
Message
The Database ID 7, Page (1:14002), slot 74 for LOB data type node does not exist. This is usually caused by transactions that can read uncommitted data on a data page. Run DBCC CHECKTABLE.
Message
Error: 7105, Severity: 22, State: 6.
[Code] ....
View 2 Replies
View Related
Feb 4, 2015
Is there any possibility to trace, most used column in every table on the SQL server?
The intention is, a query which suggests some columns to be indexed; Based on the usage of the column by the queries that run on server.
View 4 Replies
View Related
Apr 28, 2015
I need to find the one day old(in last 24hrs) user details:
Below script i am using , but its not working as expected.
select name,createdate,* from syslogins where isntgroup=0 and isntname=1 and name not LIKE ('%sa-%') and name not LIKE ('%NT %') and createdate > DATEADD(hh,-24,GETDATE())
View 6 Replies
View Related
May 5, 2015
I'm trying to run an Insert on tables using a User Login but I keep getting the error above. I ran the insert using my own credentials and it worked. The User LOGIN in question has read & write permission to the database.
View 4 Replies
View Related
Jun 15, 2015
I am trying to find out CPU utilization from the history using process.%processor time. I am having dual core CPU with 2 numa nodes each having 16 logical cpus bind to it.
how to calculate the CPU utilization using perfmon.I tried to use SQL query which gives CPU history using SQL DMV, but I am unable to get the exact value. Because in between I have used the same querry to capture my CPU usage on the run day, the value on run day and the query which iam tryting to pull out is different. I am using the same query to pull the history data with providing the date.
-- Get CPU Utilization History (SQL Server 2008 and above)
DECLARE @ts BIGINT
SELECT @ts =(SELECT cpu_ticks/(cpu_ticks/ms_ticks)
FROM sys.dm_os_sys_info);
SELECT SQLProcessUtilization AS [SQLServer_Process_CPU_Utilization],
SystemIdle AS [System_Idle_Process],
100 - SystemIdle - SQLProcessUtilization AS [Other_Process_CPU_Utilization],
[code]....
View 1 Replies
View Related
Aug 10, 2015
How to find max pool size in sql server 2012?
View 2 Replies
View Related
Sep 8, 2015
How to find available database space?
View 3 Replies
View Related
Nov 19, 2013
How to find whether an object is used by any sp , view ?
I need a query which will result the sp , view names which uses the given object name .
View 6 Replies
View Related
Dec 19, 2013
I need a query to get the first value greater than a specific threshold value
LoadTemp1 LoadTemp2 LoadTemp3 LoadTemp4 TimeStamp
300 200 100 320 12-10-2013 13:30:29
100 250 113 340 12-10-2013 14:20:12
114 339 209 345 12-10-2013 14:45:01
I need to get the first value >= 340 threshold
I need to make a Benchmark on when the first LoadTemp crosses Threshold of 340 and capture the time.
View 9 Replies
View Related
Mar 27, 2014
I need to find all the index and the creation date. I did cross apply of sys.objects & Sys.indexes on name column. I am getting some but the team is saying they created so many. Any other option to find Indexes and their creation date?
View 2 Replies
View Related
Apr 8, 2014
I have a string and i want to get only the numbers from right.
For example if I have the string Like '123756zxfggr123456' then it will show me only 123456 or if i have the string like
'4vbz67xfggr123dfd' then it will show me only 123 or if i have the string like '123756zxfgg43r5' then it will show me only 5.
I got a function where it gives me all the numbers in a string but I don't need that
CREATE FUNCTION dbo.udf_GetNumeric
(@strAlphaNumeric VARCHAR(256))
RETURNS VARCHAR(256)
AS
BEGIN
DECLARE @intAlpha INT
[Code] ....
If I ran the select statement it gives me the result 111123456 but i want only 123456 or if i select
SELECT dbo.udf_GetNumeric('111zxfggr6587fhhfkwee') AS 'Num' it will show me 6587.
View 8 Replies
View Related
Apr 16, 2014
Our company purchased a app. Is there any way to find out which store procedures were fired once a button was clicked?
View 3 Replies
View Related
May 19, 2014
What would be the most economy solution for this WHERE, I see the code where we have covnert to 101 type on both sides of equation, which I tnink is not right.
So I'm thinking to put it on the left like this, just curiouse is this the best solution, I also heard that TSQL interpret between even better , here I'm really care abour performance.
declare @DATE DATE = '01/14/2014'
--A:
SELECT * FROM TT
WHERE CAST( TT.DATETIME AS DATE) = @DATE
--B:
SELECT * FROM TT
WHERE TT.DATETIME >= @DATE and TT.DATETIME < DATEADD(D,1,@DATE)
View 3 Replies
View Related
Nov 21, 2014
We can find free space on disks with 'xp_fixeddrives'.
I need script to find all disk size(total sizecapacity) in the servers?
View 9 Replies
View Related
Jul 19, 2015
I want to send an email two ice a day, from database. So I have planned to make a storedproce which will be called by a job (which will select some record from one table and put it in other table based on a flag) but I want to run it in a transaction so that if email is send successfully then only it should commit else it should rollback.
How can i find that "Mail queued" now i should commit.
View 1 Replies
View Related
Oct 12, 2015
I have a database of 900+ tables with around 3000 SPs, and views. Manually I reviewed few tables and found that tables are not referenced with FK and I applied few. There are lots of tables and SPs using them in join statement, Is there any way with which I can get each tables missing references, any DMV or other manual script which tells about this?
View 2 Replies
View Related
Oct 20, 2015
Is there a way to find all the table names that have a foreign key that references FILE_ID_IN([FILE_ID])
Note: The table def below has a foreign key
IF object_id('CODE_MAP_IN', 'U') IS NOT NULL DROP TABLE CODE_MAP_IN
Print 'Creating table CODE_MAP_IN...'
Create table CODE_MAP_IN
(
[CODE_MAP_FR] [varchar] (53),
[CODE_MAP_TO] [varchar] (53),
[FILE_ID] [float] DEFAULT 1000
PRIMARY KEY ([CODE_MAP_FR]),
FOREIGN KEY ([FILE_ID]) REFERENCES FILE_ID_IN([FILE_ID])
) ON [PRIMARY]
GO
View 2 Replies
View Related
