Gathering Server Space
Jun 5, 2001Does any one know how to gather server size (Uses/free) space on all my servers. I would like to automate this process, some job that would go to all my server(+30), gather server size and on location.
Thanks
ADVERTISEMENT
SQL Server Admin 2014 :: Gathering Results From Extended Events Trace
Jun 16, 2015I need to find any stored procedures that have not been used over a certain time period.
I have set up an extended events session to gather sp_statement_starting and _completed.
The trace returns the object_id's of many stored procedures. I then query the sys.procedures and plug in the object_id to return the stored proc name.
Do I have to repeat this process for every different object_id or is there a way I can query the trace results, using the object_id as my search criteria as one query ?
SQL Server 2014 :: Gathering Stored Procedure Execution Time In Real Time?
Jun 11, 2015Is there a way to keep track in real time on how long a stored procedure is running for? So what I want to do is fire off a trace in a stored procedure if that stored procedure is running for over like 5 minutes.
View 5 Replies View RelatedGathering Authors In A Stored Procedure
Mar 18, 2014My stored procedure is now close to finished. The only thing left is that it prints out the "Authors" on several lines. So if a ID has several authors, it will print several lines instead of one. Can i resolve this in a Group By statement, or something else? And where should i put it?
How it is shortly showed(not with all the other stuff):
18.01.2014, Author 1, ItemID1
18.01.2014, Author 3, ItemID1
How it should be:
18.01.2014, Author 1, Author 3, ItemID1
ALTER PROCEDURE [dbo].[LOID] @month INT,
@year INT,
@report_source NVARCHAR(255),
@is_primary INT
[Code]
Designing (or Gathering Data From): A Hierarchical Database Using SQL
Feb 18, 2008Hi All,
I am attempting to create a Visual C++ application based on displaying financial charts and am using SQL Express to store Stock information such as the Exchanges the stocks are traded on, the indicessectors they belong to and the Closing prices for as long as I can download data for. I am not proficient in C++ nor SQL and am using this project to learn both languages as well as making myself rich beyond my wildest dreams.
I have "designed" a database with the following tables:
tblDate_ 1 column clmDate (Primary Key, smalldatetime, NOT NULL)
tblStockExchange_ 4 column clmStockExchangeID (PK, int, NOT NULL)
clmParentID (int, null)
clmStockExchange (nvarchar(50), NOT NULL)
clmMarkets_ (FK, nchar(20), NOT NULL)
tblMarkets_ 1 column clmMarkets (PK, nchar(20), NOT NULL)
tblIndices_ 1 column clmIndices (PK, nchar(50), NOT NULL)
tblSectors_ 1 column clmSectors (PK, nchar(50), NOT NULL)
tblMarkets_Sectors 3 columns clmMarkets_SectorsID(PK, int, NOT NULL)
clmMarkets_ (FK, nchar(20), NOT NULL)
clmSectors_ (FK, nchar(50), NOT NULL)
tblSecurities_ 4 columns clmEPIC (PK, nchar(10), NOT NULL)
clmSecurity_Type (nchar(5), NOT NULL)
clmSecurty_Name (nchar(50), NOT NULL)
clmSectors_ (FK, nchar(50), NOT NULL)
tblSecurities_Indices 3 columns clmSecurities_IndicesID (PK, int, NOT NULL)
clmEPIC_ (FK, nchar(10), NOT NULL)
clmIndices_ (FK, nchar(50), NOT NULL)
tblSecurities_Date_OHLCV 8 columns clmOHLCVID (PK, int, NOT NULL)
clmEPIC_ (FK, nchar(10), NOT NULL)
clmDate_ (FK, smalldatetime, NOT NULL)
clmOpen (float, NOT NULL)
clmHigh (float, NOT NULL)
clmLow (float, NOT NULL)
clmClose (float, NOT NULL)
clmVolume (float, NOT NULL)
Why so many tables? perhaps you should put some more in...
This was the only way I could work out how to store one-to-one and one-to-many relationships required for:
- Many closing prices for many stocks
- Stocks belonging to many indices
- Stocks belonging to only one sector
- Stocks belonging to only one market (MainMarket or AIM for LSE)
- Stocks belonging to only one Exchange (I am aware of dual listed stocks but one thing at a time)
Why nchar's and not nvarchar's?
Because I didn't realise the benefits of nvarchar's until recently. How can I change this a loose the extra spaces in the cells.
Why do some tables have IDs and others don't?
I decided to put ID columns in for tables that didn't have obvious Primary Keys - if someone could explain the advantages if ID columns I would be grateful.
To the SQL Professional's eye there will be some obvious things wrong with this design and your criticism is welcome. The database I have is achieving what I would like it to do; I can plot charts using the data but I have ran into problems when trying to create a TreeView control which is what I would like to use as a navigational tool in my application.
It would seem that pulling hierarchal data from a relational database, to pass to the TreeView control, is a tricky task to say the least. I have found many articles online which discuss how to do this (using an Adjacency List Model or Nested Set Model) but they define a fairly simple example at the beginning (based on fruit or electrical goods) but don't appear to talk about gathering data from an existing relational database or changing an existing relational database so that it is more suited to storing hierarchal information. I have Joe Celko's - Tree and Hierachies in SQL for Smarties but sadly this fine material is a little beyond me!
I would like the hierarchy to look like this:
StockExchange
Market
Sector
Stock
Indices
Sector
Stock
I have written three queries to get the StockExchangeMarketSectorStock information individually from each table but am struggling with ways to put all the rows together, add left and right values (Nested Set Model) then run queries against this to get individual nodes to pass to the TreeView control. Therefore is there something I need to add to the original design?
Any help would be greatly appreciated.
How To Replace Empty Space Or White Space In A String In A Stored Procedure
Nov 14, 2007Hi,
I am trying to do this:
UPDATE Users SET uniqueurl = replaceAllEmptySpacesInUniqueURL('uniqueurl')
What would be the syntax.
Any help appreciated.
Thanks
Mutilple Space Gets Converted To Single Space In Report Viewer Control
Feb 23, 2007I am generating a Report from Sql Data Source in Sql Server 2005 and viewing the Report in Report viewer control of Visual Studio 2005.
The data in the Data Source contains string with multiple spaces (for example €œ Test String €œ) but when they get rendered in Report viewer control, multiple spaces gets converted to single space €? Test String €œ.
I tried following solutions
1) Replacing spaces with €œ €?
2) Inserting <pre> tag before the string and </pre> tag after the string (Also tried <Pre> instead of <pre>)
But in all the cases result is same. The Report Viewer control is showing €œ €? instead of space and €œ<Pre>€? tag instead of preserving spaces.
Please provide me a solution so that spaces can be preserved in Report Viewer.
Transact SQL :: How To Find Space Available Or Send Space Alerts In Percentage
Nov 26, 2015I 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] ....
Trans Log-&>space Allocated 27GB, Space Used 100MB
Mar 2, 2005Hi.. I was doing a good maintenance on my DB and my trans log LDF keep growing until 30GB but my DB data file MDF is only 2GB. I found the two following method to reduce my log size.
Method 1: I used veritas to backup log file with truncate
Method 2: I used the shrink database option in Enterprises manager to shrink it (file chosen=log , use default option)
After doing that, I found my LDF log file is still about the same size=27GB but when I see clearly, from the shrink database windows, the log spaced used reduced to only 100MB, the allocation log space is still 27GB. Why? How to make the LDF smaller to be the around the same size as the space used 100MB?
Double Space Replaced With Single Space By Dbms ??!
Jul 20, 2005This is driving me bananas. Can't find any info on this anywhere....SQL 2000 seems to replace double space with a single space when I seta varchar field to " " (2spaces), it only stores " " (1space). Whyon earth would microsoft do this? If I save 2 spaces - I WANT TO SEE2 SPACES!!!!Can anyone help? Is this a database setting? Is this due to usingvarchar?Any help appreciated.Colin Hale
View 2 Replies View RelatedProblem With Space Allocated For Transaction Log Space
Dec 5, 2001Hello,
Somebody know how to reduce the space allocated for the transaction log space for my SQL_DB ?
3700 MB allocated but only 100 MB used and 3600 MB are free !
Transaction log properties :
Automatically grow file are filled
file growth by percent = 5%
maximum file size - restrict filegrowth = 3700 MB (we can't reduce it !)
Thank you for your precious help !
Khaix from Brussel.
Suppress Multiple Space To Single Space..
Nov 14, 2006How do we suppress multiple spaces to a single space in T-SQL
E.G.
Field: FullName
e.g.
WOMENS HEALTH RIVER VALLEY
JOHN FAMILY MED GROUP
HERSH STWEART P.
PARK HEIGHTS MEDICAL CENTER
KOPP WHITEFIELD E
The o/p wanted is
HERSH STWEART P.
Thank you.
ALTER ASSEMBLY Error Msg 6509 An Error Occurred While Gathering Metadata From Assembly ‘&&<Assembly Name&&>’ With HRESULT 0x1.
Feb 22, 2008I work with February CTP of SqlServer 2008.
I have an Assembly with several UDTs inside. Version of assembly is 1.0.*
I use CREATE ASSEMBLY statement to register this assembly, and it runs without any errors. Then I rebuild CLR solution without doing any changes in source code. In that case the only difference between new and old assemblies is version (difference in fourth part of version).
Then I try to update assembly in SqlServer. I use
ALTER ASSEMBLY <name>
FROM <path>
WITH PERMISSION_SET = UNSAFE, UNCHECKED DATA
statement for this. Statement runs with error:
Msg 6509An error occurred while gathering metadata from assembly €˜<Assembly name>€™ with HRESULT 0x1.
I found the list of condition for ALTER ASSEMBLY in MSDN:
ALTER ASSEMBLY statement cannot be used to change the following:
· The signatures of CLR functions, aggregate functions, stored procedures, and triggers in an instance of SQL Server that reference the assembly. ALTER ASSEMBLY fails when SQL Server cannot rebind .NET Framework database objects in SQL Server with the new version of the assembly.
· The signatures of methods in the assembly that are called from other assemblies.
· The list of assemblies that depend on the assembly, as referenced in the DependentList property of the assembly.
· The indexability of a method, unless there are no indexes or persisted computed columns depending on that method, either directly or indirectly.
· The FillRow method name attribute for CLR table-valued functions.
· The Accumulate and Terminate method signature for user-defined aggregates.
· System assemblies.
· Assembly ownership. Use ALTER AUTHORIZATION (Transact-SQL) instead.
Additionally, for assemblies that implement user-defined types, ALTER ASSEMBLY can be used for making only the following changes:
· Modifying public methods of the user-defined type class, as long as signatures or attributes are not changed.
· Adding new public methods.
· Modifying private methods in any way.
But I haven€™t done any changes in source code, so new version of assembly satisfies all this conditions.
What could be the reason for such behavior?
P.S. I€™ve got the same error, if I add or change any method in assembly before rebuilding.
SQL Server Space
Jun 7, 2007Dear All
My query is reagaing the SQL server space
Imagine a situation where i have purchased server space of 50 kb for my sql server
and at the time of hosting of my web site the size of the data base was 40 kb, once it is hosted
and as the data of users and visitors get stored into the database,it gets expands and the size if the size of the database exceeds the
the occupied space,will my database get stuck or..any other option to overcome this senario or Do i have to get more space periodically.
Help me out
Thank you
Sql Server Space
May 28, 2006I have a sql server 2005 DB.When I define a column as a nvarchar(50) and fill it with one character (for approx 10000 rows), how much space is taken physically on the HD?Also, if I then redefine the column (as it only contains 1 char) to nvarchar(1), is the physical size on the HD reduced immediately or do I have to perform some special action?
View 2 Replies View RelatedSQL Server Space
Jul 20, 2005I have a SQL server that is running out of log space.I have several NAS box availabe to with lots of space, is there any Ican use maped drive to store the logs in.I am not too concrned with speed
View 1 Replies View RelatedUsed Space On SQL Server
Nov 20, 2007
Hello,
We have around 500+ databases on the server, and I need to run the backup of all databases. How can I find out how much space is used on all of them? Is there any command I can issue without specifying db_name separately each time? I am trying to understand if I have available space on the disk for the backup.
SQL Server SQLMAINT And Disk Space
Oct 17, 2006Hi,
I have a 250 GB database and not much space left on the disk drive. I want to run SQLMAINT to do optimization and integrity checks on this db. My question is : How much work space does SQLMAINT need to perform these tasks?.
Thanks in advance for your help.
F.
SQL Server 2008 :: DB Showing Space Available 0.00 MB
Mar 11, 2015I've just checked the properties of one of our DB's running on SQL2008 & the properties within SSMS are showing 0.00MB space available.
The DB is setup using the simple Recovery model & is being backed up nightly.
The Data file has been set at an intial size of 9449MB with Autogrowth by 10 percent unrestricted growth.
The Log File has been set at an intial size of 14199MB with Autogrowth by 10 percent unrestricted growth.
I have checked the SQL logs and no errors are being reported. There is 79.8gb available on the partition where the DB resides.
SQL Server 2000 Running Out Of Space.
May 18, 2006Hi, we have a SqlServer instance in production containing around 10databases.It has just been realised that all the db's are held on the small c:partition with only a gig or so of space left.On the server there is another partition and another hard disk bothwill ample space (few hundred gigs).What would be the best way of getting the data onto the otherpartitions with minimal impact on the applications. Can we move theprimary data files for each db? Should we just create secondary datafiles on the big partitions for all the db's? Is there a method ofmoving all the data at once?Any ideas on how we should approach this?(ps we dont have control of the sqlserver its outsourced, so simplerthe better).Thanks,Jim
View 2 Replies View RelatedTransaction Log Backup On Server With Less HDD Space
Nov 29, 2007Hi,
I started working in one company as a DBA where the Ex-DBA left the company long time back. So all the databases have huge transaction logs (50-80GB) in size. But the problem is-
1. HDD space on the m/c its only 45GB. When I ran TLog backup cmd on that m/c, the hdd space was started reducing around 1GB/min and in few minutes I saw hdd space left is only 15GB so I stopped cmd (It had completed only 37% of the backup).
Second time again I tried to take TLog backup on hdd placed on n/w path but again I saw it was eating local disk space very fast.
So my question is, Is it like when you take Tlog backup of size say 80GB it will use 80GB local hdd as a temporary storage?
2. For this I found 1 workaround- I took Full backup then I restored it on some other m/c, then shrun the log files to few MB's then I again took Full backup of this shrunk db and restored it on production.
Is this only solution to my problem(Very less space on production's local HDD)? If anyone knows better soln than this then plz suggest me.
Thanks in Advance.
How To Increase Disk Space In Server
Aug 11, 2015How to increase disk-space of database in sql server?
View 4 Replies View RelatedWhat Is This Box Type Character For A Blank Space In SQL Server?
Mar 8, 2007Hi,
I am using DTS for importing a table from FileMaker Pro (a database application) to SQL Server 2000. The problem is that everything gets imported correctly but in some values, it shows a box type character for a blank space. It does not happen for all the blank spaces. Because of this, that value is cannot be checked in the WHERE clause until I use a LIKE and % for that blank space.
I copied the value and pasted in Notepad, it only shows a blank space. What is this character?
Can someone help...
Thanks,
bullpit
SQL Server Is Occupying Large Memory Space
Oct 3, 2001In an Intranet Application using Win NT, Apache, Tomcat and SQL Server, the memory space used by SQL Server is drastically increasing and finally the system crashes. Nearly 40 people are accessing the system. The hardware configuration is P2 processor with 393 MB RAM and 2GB Virtual Memory. SQL Server,Web server and Servlet Engine are running on same machine.
Within three hours, SQL Server occupies 200M memory and the system perfomance comes down and finally the system stopes the tomcat servlet engine.
Anybody have any idea on this? We have nearly 1500 JSP pages,200 Bean files and 300 tables in SQL Server.
Server Up Time And Disc Space Alerts??
Jan 13, 2000Hi,
How can i find the server uptime and
How can i get the alert when disc space fill around 80%.
Please help me!!!
dindu
Question On Data/log Space Allocation By SQL Server
Oct 20, 1999I have a quick question maybe someone can answer me:
Suppose I have a database with data and log on same device, by default,
3 segments are created: system,default and logsegment.
Is the allocation , or any limit, among these segments(i.e. for system table, other table and indexes , log) done by the system ? or will the space required be allocated when they are needed ?
(i.e. I can have a situation of 10% database space used for data, 90% used for logging and I can also have 90% used for data, 10% used for log ?)
Is there any control us we can make ?
Of course, for recovery and performance reason, it is always recommended to have data and log on 2 diff. device.
SQL Server Reports Incorrect "Data Space Available"
Mar 11, 1999Thanks for anybody that can help me with this.
When I double click on a database in the Enterprise Manager and the "Database Space Available" (in the Database tab) always shows 0.00 MB even though there is space available. This only happens to one of our database. The data device is 1.8GB and log device is 360MB and I've allocated all the space from device to database.
We're using SQL Server 6.5 sp3 in NT 4.0 sp3.
Anybody knows why is SQL Server reporting 0.00MB when you know there is space avaialble. I need to know the data space available and log space available so that I can expand it when the space runs low. Please help.
Thank you,
Jimmy Jen
SQL Server 2008 :: How To Convert Free Space Value To MB Or In GB
Mar 9, 2015wmic logicaldisk where "DeviceID='C:'" get FreeSpace /format:value
FreeSpace=231379468288
Identifying The Remote SQL Server Disk Space?
Dec 6, 2007
How do we identify the total disk space of a SQL Server Remotely. The procedure xp_fixeddrives gives the available free space .. but I would like to know the total disk capacity .. Any pointers?
Thanks,
Deepti
Disk Space Information In SQL Server 2005.
Aug 4, 2007As a normal sql user we are not able to get the disk space information by
executing 'xp_fixeddrive' extended stored procedure.
We are able to get the result using sa user and windows authenticated user.
Any help will be appreciated.
SQL XML :: Server Is Removing First Character Of Values If It Is A Space
Jun 3, 2015I'm creating a table using a XML string and a stored procedure to the SQL Server. I'm having problems in just one field, it is a nvarchar(10) type. This field's string XML Schema is also nvharchar(10) type. It is called CodCanalPrice.
Today, the only values this field can assume are AA or BB or CC, (...), ZZ or (space)A, (space)B, (...), (space)C. Note that (space) is actually a space character. They all have 2 characters.
The XML data string of one data row is showed below:
<row id="1" (...many fields...) CodCanalPrice=" A" />
When the statement is executed, SQL server removes the first space (so in this case it is updated to A without space in the table). The others values that don't have an space before like AA or BB are updated fine. All others fields are also updated correctly.
So far I tried to replace the first space for  but SQL Server doesn't accept this tag.
Sql Server 2000 - Spid Using Most Tempdb Space ?
Jan 29, 2008Hi There
In Sql Server 2005 sys.dm_db_task_space_usage is great for tracking down temdb space issues.
I have tempdb space issues in sql server 2000, how do i get the same info in SS2000 ? IE: What spid is using the most tempdb space ?
Thanx
Know The Hard Disk's SQL Server Space Available From SQL Query
Oct 5, 2007
I would be able to determine how much hard disk space is available on the Server with a SQL Query, and from an other computer on the LAN.
How can I do something?
Thanks for your help.