Hello,
I am managing a sqlserver 6.5 database in my company. I get the message that the datafiles should be expanded but whenever I try to expand it the following message appears:
Could not find enough space on disks to extend the database. Meanwhile, I have about 6 gigabytes free space on my disks. Please help me out.
Thanks,
Albert
Hi,
I have to rebuild a clustered index.I want to know how to find out the free space in a database ?
I know that DBCC SQLPERF ( LOGSPACE )
will give free space in a log space.
Thanks,
copernicus.
How to find available database space?
View 3 Replies View RelatedThe sp_spaceused proc seems to have been removed in 7.0?
Dbcc sqlperf also seems to have been removed?
Surely there must be a counterpart - which is?
I like to have a script which shows the space usage
especially on a table basis for a periodic report.
My group is trying to ensure that there is a sufficient amount of cushion between the space allocated and the current size of a database. I know that I can check this using the Enterprise Manager. Is there a stored procedure or a systems table that holds this information. I know that sp_spaceused will give me the unallocated space, but i want the allocated space. Any suggestions?
View 2 Replies View RelatedI am using the below script to get space alerts and now i am interested in sending alerts if for any drive space available is Less than 10% or 15%.. how to convert beelow code to find in %
Declare @Drives Varchar(20)
DECLARE @Spaces Varchar(50)
DECLARE @availableSpace FLOAT
DECLARE @alertMessage Varchar(4000)
DECLARE @RecipientsList VARCHAR(4000);
CREATE TABLE #tbldiskSpace
[Code] ....
Hi All
I am in process of moving a SQL 2005 solution from a development box that used local storage to UAT environment with SAN attached storage. The solution uses database snapshots
The database files are on the SAN storage but during testing I was unable to create a Database snapshot on the SAN disk. Creating snapshots on the local disk worked fine.
Is their some restriction/problem in using the database snapshot technology with SAN storage?
I have a database that is in production. It is almost full. How would I go about extending the size of it? Would it involve an outage?
View 4 Replies View RelatedI am testing out a blank database created over two physical files on two separate disks with one table called data which has one column called values nvarchar(max).
I filled the table up with a whole load of data and ran a select * against it. If I run Permon at the same time I can see that the read load has been spread over multiple disks as each of these disks is getting read from in parallel. If I create the same database on a single file and run the same select * again it takes much longer, proving that the read load has been distributed across multiple disks.
Now moving onto writes, this is where the confusion lies. I understand that SQL server fills files evenly until they need growing, after which it will then fill files individually until they are full in a round robin fashion unless you have trace 1117 turned on. What I don't understand is why the writes aren't distributed out whilst it is filling these file groups.
I ran an continual insert into my table with go 1000000 to monitor how the files are being filled up. I monitored where SQL server was physically placing the files as they were being inserted by running the following query:
;WITH CTE AS
(SELECT
sys.fn_PhysLocFormatter (%%physloc%%) col1,
RIGHT(LEFT(sys.fn_PhysLocFormatter (%%physloc%%),2),1) AS [Physical RID],
DATAID
[Code] ....
I could see that it would a thousand or so records into file 1, then a thousand or so into file 2, then a thousand or so into file 1 etc etc. In another words it would hit one disk, then another disk, then back to disk one to fill the file evenly. Is there any way to make SQL Server distribute the writes out in parallel so that both disks are writing in tandem?
By the looks of it, multiple disks only scale reads, as with writes only one disk is ever written to at once which is annoying. Any way to harness the write power of multiple disks?
I need a way to get the used space for a physical drive, or even the total capacity for a drive to do some calculations.
I don't want to have to write my own native code either.
Is there a stored procedure that will give this to me?
I saw the xp_fixeddrives that will give the free space, but I need one of the other as well.
Any way to get this?
Thanks,
Jason
How do I find the available free space in the database, in the Database and the Log Segment
View 1 Replies View Relatedhow can I find a space into a string ? (for MS SQL 2005)SELECT name FROM tbNames WHERE name LIKE '% % 'is not workingthank you
View 7 Replies View RelatedWhat is the command to find the space utilised by the log file , mdf file in sql server
I'm trying to determine how much space some tables use (SQL2000), and I found 2 suggestions posted earlier, but can't get them to work for me.
The first was
"Right click on the DB in Enterprise Manager, select view then taskpad."
When I try that, I get some of the tables displayed with the info I want, but I can't see them all and can't scroll down beyond the first 22 tables in the database.
The second suggestion:
exec sp_msforeachtable @command1="print '?'",@command2="sp_spaceused '?'"
It says "command copleted successfully, but where does the output to this go ?? Is there something other than "print" I should use ?
The grid pane is empty.
Hey - I know that I can find space information about SQL Server. Allocated space, free space, used space, data space, etc... BUT is there a way that I can query how much total/available space is on the actual drive? For example, let's say that I have SQL installed on the D drive of a Server; i also have another application on that drive. I know that I can query how much room SQL Server is using, but can i query how much total/available space is on the drive? Any help appreciated.
View 1 Replies View Related
I need a query that checks each row in a column (hardness) to see if the last character is a "space". The column holds numbers (73.45) but is a varchar 50 type. It will always be the 5th character but it would be nice to not make this assumption. Please help!!
Thanks.
How to find rowcount and size(space used) for all tables in a db? Can any one give me the script please?
Thanks,
Hi all,
I have to find free disc space in a drive from SQL Query and i am using MSSQL200. Does any body know the command for the same.
Also if i want to find hard disk space of a different machine on network, it is possible to get the data??
Also can i get the CPU/Memory usage data of our machine by some SQL/C++ commands??
Please let me know if u know any of the answer...
thanks
Alok
i need to find and all special characters and replace with space.
Following query works well for me to finding all rows having special char.
SELECT DESC FROM TBL_DESC
WHERE DESC LIKE '%[^A-Z0-9 ]%'
replacing special char with space . i need to remove special chars only.
How do i find Total allocated space and used space of a memory optimized filegroup?
use memory_optimized_db
Go
select (SUM(size)*8.0)/1024.0 as Space,
FILEGROUP_NAME ( data_space_id ) , type_desc from sys.database_files
group by data_space_id,type_desc;
above query gives "current used size of the container " of memory optimized file group but doesn't give Total space detail.
Are there any issues with installing SQL 2000 on a server with Dynamic Disks configured with RAID 1 Mirroring ? I know that with Dynamic Disks there are no partitions but rather volumes. Is the drive configuration setup the same way ?. Would I setup a volume for the O/S and a volume for SQL ? The reason I need to use Dynamic Disks is because we will be integrating it later with an EMC SAN solution.
View 1 Replies View Related
Can both be used with SQL server ? Dynamic disks provide more flexibility, is there performance imact ?
Thank you.
Hello,
I have a two node SQL 2000 cluster running on windows 2003 enterprise server. We need to replace the SAN disks. Can we not disable SQL service & Cluster service, copy the contents from existing disks to target disks, swap the drive letter & start the services?
What is the best practice to do this? Appreciate your help.
Thanks
Rajan
I have a new server.
It was shipped with a 76 gig drive setup in RAID 1 (2 disk) and a 400 gig drive setup in RAID 5 (4 disk).
I would like to determine what is the best way to setup up the partitions. What size and what should be placed on each.
Like the C: Drive...Should I just put Windows on there and nothing else? Do I stand to gain something from not using part of that 76 gigs as a D: drive for my apps?
Any help appreciated!
Is there any performance enhancements to be gained by storing frequently 'trigger-written-to' databases on a seperate disk to the source database? In particular, we keep a 'history' database of all inserts/updates/deletes against records, activated by triggers, and I was wondering if I would gain performance enhancement by locating the two databases on different disks?
Thanks in advance
I am trying to build the 2 node 2 clusters with the AlwaysOn.
Here isthe landscape.
2 nodes PROD failover cluster (running once instance)
2 nodes DR failover cluster (running 2 instances - DR and PRE-PROD)
Both clusters are in different geographies.
PRE-PROD can be editable. So out of scope of Always On.
One instance on PROD -> DR of the other box. [Want to achive thru AlwaysON]
Now my Question:
1) Do i need to have all the 4 nodes in same failover cluster group? If yes, then this would become MultiSubnet cluster Or Is there any way those 2 diffrerent failover clusters (one DR and one PROD) can be part of AlwaysOn.
2) Can i use the clustered disks as in the above landscape for always on?
Hi all
Iam working in Prodcution ENV,Please help how make space
The log file for database 'Home_alone' is full. Back up the transaction log for the database to free up some log space.
HelloIn my website I have a textbox to search some products, but it would like to extend my search-function so that it could find special characters. For example:When I search Mexico, I would like to find México. Is there an easy way to do this?Thanks in AdvanceWesley
View 1 Replies View RelatedI've read that if particular tables are frequently queried together through a join then these tables should be placed on different devices on different physical disks.
What does this mean exactly and how would you configure this?
Is this a common practice in high-performance real-world environments (or should it be)?
OS Windows Server 2012
SQL 2012
I have a software that presents a drive to a 2 node cluster.
Not sure how I would go about presenting this newly presented drive as a Cluster Resource and make SQL depend on this resource.
Hi
Had a weird error yesterday, can anybody shed any light on it.
I got the following error in a SQL 7(sp2) database.
'The log file for database 'IT2PROD' is full. Back up the transaction log for the database to free up some log space..
Error: 9002, Severity: 17, State: 2'
The Transaction Log has unrestricted file growth and auto grow file set and the disk has over 20gb of free space.
The only explanation I can think of is that the Log tried to extend during the hourly backup. Would this cause the above error ??? The backup file is usually about 80mb but was over 300mb when this happened.
Any ideas ????
Thanks in advance
Gary
Hi All,
I'm rather new to the MS SQL Server development in general and especially to its data security architecture and features - I'd like to know if it is possible for end-user to retrieve/update(!?) the data using a SP which executes on a table for which she/he doesn't have any privileges.
TIA,
Shamil
I found this script for dependencies and want to extend it:
I want to add the obj_typ (U, FN, P, V, TR...) for the dependency
How can I do this?
Here is the script:
WITH DepTree (referenced_id, referenced_name, referencing_id, referencing_name, NestLevel, obj_type )
AS
(
SELECT
o.[object_id] AS referenced_id ,
o.name AS referenced_name,
[Code] ...